Post reply

split 1 column into 3 columns:

  • May 23, 2011 at 10:50 am

    #251485

    Hi Everyone,

    How can I break Column C :

    A - B - C - D

    1 - 61 - 1:00 -5/10/2011 2:00

    1 - 61 - 2:00- 5/10/2011 2:00

    1 - 61 - 3:00- 5/10/2011 2:00

    2 - 61 - 2:00- 5/10/2011 4:00

    2 - 61 - 3:00- 5/10/2011 4:00

    2 - 61 - 4:00- 5/10/2011 4:00

    INTO this:

    A - B - C1 -c2 -c3 - D

    1 - 61 - 1:00 -2:00- 3:00 - 5/10/2011 2:00

    2 - 61 - 2:00 -3:00- 4:00 - 5/10/2011 4:00

    Thanks for your help.

  • May 23, 2011 at 10:54 am

    #1328591

    Will all A B combination have only 3 rows each ?

  • May 23, 2011 at 11:07 am

    #1328602

    For your sample data:

    declare @testtable table

    ( A int, B int, C time , D datetime)

    insert into @testtable values

    (1 , 61 , '1:00' ,'5/10/2011 2:00')

    ,(1 , 61 , '2:00', '5/10/2011 2:00')

    ,(1 , 61 , '3:00', '5/10/2011 2:00')

    ,(2 , 61 , '2:00', '5/10/2011 4:00')

    ,(2 , 61 , '3:00', '5/10/2011 4:00')

    ,(2 , 61 , '4:00', '5/10/2011 4:00')

    ; WITH CTE AS

    (

    SELECT A , B , c , D ,

    RN = ROW_NUMBER() OVER( PARTITION BY A , B , D ORDER BY C )

    FROM @testtable

    )

    SELECT A , B ,

    C1 = MAX( CASE WHEN RN = 1 THEN C ELSE '0:00' END )

    ,C2 = MAX( CASE WHEN RN = 2 THEN C ELSE '0:00' END )

    ,C3 = MAX( CASE WHEN RN = 3 THEN C ELSE '0:00' END )

    ,D

    FROM CTE

    GROUP BY A , B , D

  • May 23, 2011 at 11:08 am

    #1328603

    No, it can change.

    thanks.

  • May 23, 2011 at 11:15 am

    #1328606

    SQL_Nw (5/23/2011)

    No, it can change.

    thanks.

    So you will need, C1, c2, c3, c4, c5.... etc etc depending on the rows, right?

  • May 23, 2011 at 11:25 am

    #1328613

    Sorry, i was not clear, it will always be C1 c2 c3.

    Thanks

  • May 23, 2011 at 11:29 am

    #1328618

    Thanks.

    I used your query and it did break the column into 3 columns.

    However the data is being replaced as 0:00 at most of the places.

    While it should be

    1- 61- 1:00- 2:00- 3:00 - 5/10/2011 2:00

    what am I missing.

    Thanks..

  • May 23, 2011 at 11:47 am

    #1328631

    Change the datatype of the column C in the table variable to VARCHAR(5)...

  • May 23, 2011 at 1:30 pm

    #1328681

    My procedure worked 🙂

    I spent almost 2 days trying to figure this out. I could not have done it without your advice.

    thanks so much for your help.

  • May 23, 2011 at 2:14 pm

    #1328702

    So that someone in the future that stumbles across this thread knows, can you post your procedure here so that it can be referenced?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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