Select distinct values from multiple columns in same table

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
A123321 01/01/2013 02/01/2013 03/01/2013 10/01/2013
B890098 01/02/2013 06/02/2013 08/02/2013 11/02/2013
C678876 01/01/2013 08/02/2013 11/02/2013 03/03/2013
D342243 07/09/2013 09/09/1023 10/09/2013 11/09/2013

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]


UNION

SELECT DISTINCT [Payment Date] FROM [dbo].[Order]

UNION

and so on….

This can be done concisely using UNPIVOT as follows.

SELECT DISTINCT Dates from
(SELECT [Order Date]
,[Payment Date]
,[Shipped Date]
,[Feedback Date]
FROM [MyDb].[dbo].[Order] ) p
UNPIVOT (Dates FOR UniqueDates IN
([Order Date]
,[Payment Date]
,[Shipped Date]
,[Feedback Date])
) 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s