Selecting a distinct value from a column is no rocket science, but what if you want distinct values from two, three or more columns from a table. I don’t want a distinct combination of values from multiple columns (which will what DISTINCT col1, col2,col3 etc. would do), I want distinct values from first column, then the second, then the third and so on.
The simplest way would be to do SELECT DISTINCT on each column and then UNION them all. But I was looking for something a bit more concise; my table apparently had ten date columns wherein I wanted to find all the unique dates. Enter PIVOT/UNPIVOT twins. If you think about it all we need to do is UNPIVOT the columns we are interested in and then select DISTINCT on them. Simples!. Of course, you would have to cast them to same data type; but I suppose that’s fairly straightforward. So here is an example.
Consider a table like this
|Order Number||Order Date||Payment Date||Shipped Date||Feedback Date|
Lets say we want to find all the unique dates in this table. I repeat, we don’t want to find combination of unique dates, which is what DISTINCT would do, we want to find unique dates in all the columns. As mentioned before, we can do it using DISTINCT & UNION as follows
SELECT DISTINCT [Order Date] FROM [dbo].[Order]
SELECT DISTINCT [Payment Date] FROM [dbo].[Order]
and so on….
This can be done concisely using UNPIVOT as follows.
SELECT DISTINCT Dates from
(SELECT [Order Date]
FROM [MyDb].[dbo].[Order] ) p
UNPIVOT (Dates FOR UniqueDates IN
) AS unpvt
If the columns had different data types, you would have to cast them but the approach essentially remains same. Hope that was helpful and comments are always welcome.