Distinct-tify TWO columns into ONE column

Question

Post reply

Distinct-tify TWO columns into ONE column

rktn_odin

SSChasing Mays

Points: 636
More actions
November 23, 2011 at 8:01 pm

#263385

I have this data:
Date1 || Date2
2011-10-31 00:00:00.000 || 2011-11-01 00:00:00.000
2011-11-01 00:00:00.000 || 2011-11-01 00:00:00.000
2011-11-01 00:00:00.000 || 2011-11-02 00:00:00.000
2011-11-02 00:00:00.000 || 2011-11-02 00:00:00.000
2011-11-03 00:00:00.000 || 2011-11-03 00:00:00.000
2011-11-04 00:00:00.000 || 2011-11-04 00:00:00.000
2011-11-07 00:00:00.000 || 2011-11-07 00:00:00.000
2011-11-07 00:00:00.000 || 2011-11-08 00:00:00.000
My expected output is:
DateOutput
2011-10-31 00:00:00.000
2011-11-01 00:00:00.000
2011-11-02 00:00:00.000
2011-11-03 00:00:00.000
2011-11-04 00:00:00.000
2011-11-07 00:00:00.000
2011-11-08 00:00:00.000
I appreciate any help. Thanks 😉
Edit:
I've added one more data so that problem is more clear.
Edit2:
So sorry lol, I made mistake again. Please see the new expected result.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Look up UNION.

--First, let's setup your sample data

DECLARE @TABLE AS TABLE (Date1 DATETIME, Date2 DATETIME)

INSERT INTO @TABLE

SELECT '2011-11-01 00:00:00.000', '2011-11-01 00:00:00.000'

UNION ALL SELECT '2011-11-01 00:00:00.000', '2011-11-02 00:00:00.000'

UNION ALL SELECT '2011-11-02 00:00:00.000', '2011-11-02 00:00:00.000'

UNION ALL SELECT '2011-11-03 00:00:00.000', '2011-11-03 00:00:00.000'

UNION ALL SELECT '2011-11-04 00:00:00.000', '2011-11-04 00:00:00.000'

UNION ALL SELECT '2011-11-07 00:00:00.000', '2011-11-07 00:00:00.000'

UNION ALL SELECT '2011-11-07 00:00:00.000', '2011-11-08 00:00:00.000'

--Now on to your query

SELECT Date1

FROM @TABLE

UNION

SELECT Date2

FROM @TABLE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 2

SELECT Date2 AS DateOutput FROM YourTable ??

It's most probably not the code you're looking for, but it's a valid solution based on your sample data... You might want to be a little more specific about what you're looking for.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

rktn_odin SSChasing Mays Points: 636 More actions · Answer 3

LutzM (11/24/2011)
SELECT Date2 AS DateOutput FROM YourTable ??
It's most probably not the code you're looking for, but it's a valid solution based on your sample data... You might want to be a little more specific about what you're looking for.

Sorry for that.

I've added one more data.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

rktn_odin (11/24/2011)
LutzM (11/24/2011)
SELECT Date2 AS DateOutput FROM YourTable ??
It's most probably not the code you're looking for, but it's a valid solution based on your sample data... You might want to be a little more specific about what you're looking for.
Sorry for that.
I've added one more data.

OK. . . again, look-up UNION 😉

--First, let's setup your sample data

DECLARE @TABLE AS TABLE (Date1 DATETIME, Date2 DATETIME)

INSERT INTO @TABLE

SELECT '2011-10-31 00:00:00.000', '2011-11-01 00:00:00.000'

UNION ALL SELECT '2011-11-01 00:00:00.000', '2011-11-01 00:00:00.000'

UNION ALL SELECT '2011-11-01 00:00:00.000', '2011-11-02 00:00:00.000'

UNION ALL SELECT '2011-11-02 00:00:00.000', '2011-11-02 00:00:00.000'

UNION ALL SELECT '2011-11-03 00:00:00.000', '2011-11-03 00:00:00.000'

UNION ALL SELECT '2011-11-04 00:00:00.000', '2011-11-04 00:00:00.000'

UNION ALL SELECT '2011-11-07 00:00:00.000', '2011-11-07 00:00:00.000'

UNION ALL SELECT '2011-11-07 00:00:00.000', '2011-11-08 00:00:00.000'

--Now on to your query

SELECT Date1

FROM @TABLE

UNION

SELECT Date2

FROM @TABLE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 5

@cadavre: Your code returns 2011-10-31 which is not part of the expected result.

I'd throw a DISTINCT into the ring:

SELECT DISTINCT Date2 AS DateOutput FROM @TABLE

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

LutzM (11/25/2011)
@Cadavre: Your code returns 2011-10-31 which is not part of the expected result.

He added the 31st when you mentioned that he didn't actually need to "distinct-tify" his two columns. I've assumed he forgot to add it to the expected result.

The title of his question is "Distinct-tify TWO columns into ONE column", so I feel it's a safe bet that he wants UNION 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 7

There's only one person being able to answer that 😉

But the chances on your side aren't too bad...

If all the code I provided does is to show how important consistent sample data are, then the lines are not wasted 😎

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

LutzM (11/25/2011)
There's only one person being able to answer that 😉

Agreed.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rktn_odin SSChasing Mays Points: 636 More actions · Answer 9

Hi Cadavre and everyone!

Thanks for your help! I really appreciate it.

I didn't know a simple UNION approach will solve this problem. Now I know the difference between UNION and UNION ALL.

Thanks again!