Post reply

Distinct-tify TWO columns into ONE column

  • 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.

  • November 24, 2011 at 1:53 am

    #1412578

    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/

  • November 24, 2011 at 2:34 pm

    #1412797

    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]

  • November 24, 2011 at 6:08 pm

    #1412827

    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.

  • November 25, 2011 at 4:06 am

    #1412941

    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/

  • November 25, 2011 at 9:26 am

    #1413016

    @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]

  • November 25, 2011 at 9:39 am

    #1413020

    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/

  • November 25, 2011 at 9:51 am

    #1413022

    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]

  • November 25, 2011 at 9:54 am

    #1413023

    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/

  • November 27, 2011 at 6:34 pm

    #1413216

    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!

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

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