I have a list with two columns. Column names are Username and Status (options Free and Busy). Usernames can have both Free and Busy status or only one type Free or Busy.

To make more clear this is example:

Username     |  Status
Peter Parker |  Busy
Peter Parker |  Free
Hal Jordan   |  Free
Clark Kent   |  Busy
Clark Kent   |  Free

Now I need a view to show only Hal Jordan as he is only one with unique status. I know to do this with two WFs, one comparing usernames (in another list where are just usernames) for each item and then triggering another WF to update hidden column in next item so first WF can check that item next and so on, but it takes a year to complete and it needs to be refreshed on item created/changed.

Do you have any idea how to do this on easy way? (exp. Like in excel when you can filter columns)


SharePoint List views are based on CAML queries and CAML does not support retrieving DISTINCT values yet.

What you can do is use GROUP BY, which will group all the similar results together and you can optionally collapse it as well, looking like a distinct rows.

Reference link

  • If I understood you correct, you suggested me to group results status exp. Free. Which wouldn't give me correct result because I would see also users that have Free and Busy status also, like I showed in example in question. I only need users that have only one status Free or Busy, not both.
    – Danilo
    Aug 3 '15 at 7:30

You can use DataView.ToTable()- its first parameter(if true) makes the list distinct.

SPList list = SPContext.Current.Web.Lists["ListName"];
SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='Username' /></OrderBy>";

DataTable tempTbl = list.GetItems(query).GetDataTable();
DataView v = new DataView(tempTbl);
String[] columns = {"Username"};
DataTable distinctTbl = v.ToTable(true, columns);

You can then use this DataTable distinctTbl as you want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.