Search code examples
t-sqlreporting-servicesssrs-2012distinct-values

Generate DISTINCT Lists with ID T-SQL

For use in SSRS multi-select dropdowns, I need to generate multiple DISTINCT lists of values.

This is easily done. Select DISTINCT Department from DimEmployee

In order to set an SSRS default value, you must have an ID field also in your data set.. this is not achieved by the above query

I need to assign an arbitrary uniqueID to each of these records from my distinct list

Any advice appreciated.

Final output would be:

Select * From dsDeptList

Results...

ID   |   DeptName

1    |   DeptName1
2    |   DeptName2
etc.....
Solution

  • Just include the EmployeeID column twice (with column aliases). Use it as the ID and the value. The DISTINCT guarantees it's unique in the resultset.