One of the many requests I have seen from various people is how to get the equivalent of the SQL Distinct query in SharePoint. This actually can be really easy to achieve and I will show you how to do make a dropdown list which only contains unique values.

If you look at my previous post on how to create a dropdown list from a SharePoint list before hand as this article follows directly on.

The section we really need to look at is the following.

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row" />

This variable holds the dataset that SharePoint Desginer uses for it’s templated DVWP’s, by altering this we can change what information is displayed. To make this work we will need to sort the data on the column we are going to be filtering, in this example I am using the Title field for simplicity.

• If you click the small cheveron for the DVWP and then click Sorting and Groups.
• Add the Title Column into the sort order, make sure it’s set to Ascending and click OK.

This will order the Title Column so any repeated entries are listed together in alphabetical order.  The next thing we need to do is to alter the $Rows variable to filter out duplicates from out selection. 

To do this change line noted earlier

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[not (@Title=preceding-sibling::Row/@Title)]" />

This XSL will filter out any duplicate entries in the Title Column on your list, you can alter the field that you use but you will need change both of the @Title entries in the Rows variable and remember to sort the webpart by Ascending Order on the column you are using. You will also need to alter the following part of the template to change what values are displayed in the dropdown list.

<xsl:template>
  <option>
    <xsl:attribute>
      <xsl:value-of select="@ID"/>
    </xsl:attribute>
    <xsl:value-of select="@Title" />
  </option>
</xsl:template>

If you need to display your column simply change the @Title to the column you wish to use. I hope you find this useful and hopefully I can post up my Javascript to handle cascading dropdown lists in the future.