Get distinct values in share point 2010 list using CAML query

Get distinct values in share point 2010 list using CAML query


Step 1


Create a list in Sharepoint 2010 site, here I have created a list called "CAMLTEST" and entered three items in to the list

Here the Name "Rajesh" is repeated.

Step 2


I have created a visual Web part with a label - Name, drop down - ddl_names and a button - "btn_binddropdown". It will show the distinct values from the list.

Step 3


Write in the btn_binddropdown button click event...


 protected void btn_binddropdown_Click(object sender, EventArgs e)
        {
            try
            {
                SPList oList = SPContext.Current.Web.Lists["CAMLTEST"];
                SPQuery query = new SPQuery();
                query.Query = "<OrderBy><FieldRef Name='Name' /></OrderBy>";
                DataTable dtcamltest = oList.GetItems(query).GetDataTable();
                DataView dtview = new DataView(dtcamltest);
                DataTable dtdistinct = dtview.ToTable(true, "Name");
                ddl_names.DataSource = dtdistinct; 
                ddl_names.DataTextField = "Name";
                ddl_names.DataValueField = "Name";
                ddl_names.DataBind();                           
                               


            }
            catch (Exception exe)
            {
                Response.Write("Exception at btn_binddropdown_Click :" + exe);
            }
       }

Step 4


Deploy the webpart to the sharepoint site required (the site should contain the "CAMLTEST" list).

Insert the wepart in to any page and click the binddropdown button, the dropdown will be populated with the distinct values of name column in CAMLTEST list.

Happy Coding :))))))))))))))