Columns to Rows

  • texpic

    SSCertifiable

    Points: 5882

    Looked all over, found rows to columns can't find this.

    Desired Result (notice A distinct) two fields, one with id, other with data and comma between

    IDID DATA

    A P1, P2, P3, P4

    B P2

    C P3, P4

    D P3, P5

    CREATE TABLE #abc

    (IDID varchar(1), Data varchar(2))

    INSERT INTO #Unpivot

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P3' UNION ALL

    SELECT 'A', 'P4' UNION ALL

    SELECT 'B', 'P2' UNION ALL

    SELECT 'C', 'P3' UNION ALL

    SELECT 'C', 'P4' UNION ALL

    SELECT 'D', 'P3' UNION ALL

    SELECT 'D', 'P5'

  • Dwain Camps

    SSC Guru

    Points: 86893

    Probably something like this.

    WITH SampleData (ID, Data) AS

    (

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P3' UNION ALL

    SELECT 'A', 'P4' UNION ALL

    SELECT 'B', 'P2' UNION ALL

    SELECT 'C', 'P3' UNION ALL

    SELECT 'C', 'P4' UNION ALL

    SELECT 'D', 'P3' UNION ALL

    SELECT 'D', 'P5'

    )

    SELECT ID

    ,D1=MAX(CASE rn WHEN 1 THEN Data END)

    ,D2=MAX(CASE rn WHEN 2 THEN Data END)

    ,D3=MAX(CASE rn WHEN 3 THEN Data END)

    ,D4=MAX(CASE rn WHEN 4 THEN Data END)

    ,D5=MAX(CASE rn WHEN 5 THEN Data END)

    FROM

    (

    SELECT ID, Data, rn=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Data)

    FROM (

    SELECT DISTINCT ID, Data

    FROM SampleData

    ) a

    ) a

    GROUP BY ID;

    If you need to define your columns dynamically, read this: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • PravB4u

    Default port

    Points: 1401

    try this

    Select distinct IDID, Data= REPLACE((Select distinct t1.data as [data()]

    From #Unpivot t1 Where t1.idid = t2.idid Order by t1.data FOR XML PATH ('') ), ' ', ',')



    Praveen D'sa
    MCITP - Database Administrator 2008
    http://sqlerrors.wordpress.com

  • ashokpothuri123

    SSC Enthusiast

    Points: 133

    This should work for you

    SELECT

    ID

    ,LEFT(dd.Data_Values, LEN(dd.Data_Values) - 1) Data

    FROM

    #Unpivot up

    CROSS APPLY ( SELECT

    CAST(DATA AS VARCHAR) + ','

    FROM

    #Unpivot up1

    WHERE

    up1.ID = up.ID

    FOR

    XML PATH('') ) dd ( Data_Values )

    GROUP BY

    ID

    ,dd.Data_Values

  • Luis Cazares

    SSC Guru

    Points: 183633

    I'm just modifying Dwain's query to reduce the code by using dense_rank.

    WITH SampleData (ID, Data) AS

    (

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P3' UNION ALL

    SELECT 'A', 'P4' UNION ALL

    SELECT 'B', 'P2' UNION ALL

    SELECT 'C', 'P3' UNION ALL

    SELECT 'C', 'P4' UNION ALL

    SELECT 'D', 'P3' UNION ALL

    SELECT 'D', 'P5'

    )

    SELECT ID

    ,D1=MAX(CASE rn WHEN 1 THEN Data END)

    ,D2=MAX(CASE rn WHEN 2 THEN Data END)

    ,D3=MAX(CASE rn WHEN 3 THEN Data END)

    ,D4=MAX(CASE rn WHEN 4 THEN Data END)

    ,D5=MAX(CASE rn WHEN 5 THEN Data END)

    FROM

    (

    SELECT ID, Data, rn=DENSE_RANK() OVER (PARTITION BY ID ORDER BY Data)

    FROM SampleData a

    ) a

    GROUP BY ID;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares

    SSC Guru

    Points: 183633

    And now correcting previous posted queries as they had syntax errors.

    Select distinct IDID,

    Data= REPLACE((Select distinct t1.data as [data()]

    From #Unpivot t1

    Where t1.IDID = t2.IDID

    Order by t1.data FOR XML PATH ('') ), ' ', ',')

    FROM #Unpivot t2

    SELECT IDID

    ,LEFT(dd.Data_Values, LEN(dd.Data_Values) - 1) Data

    FROM #Unpivot up

    CROSS APPLY ( SELECT DISTINCT CAST(DATA AS VARCHAR) + ','

    FROM #Unpivot up1

    WHERE up1.IDID = up.IDID

    FOR XML PATH('') ) dd ( Data_Values )

    GROUP BY IDID ,dd.Data_Values

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Dwain Camps

    SSC Guru

    Points: 86893

    Luis Cazares (11/19/2013)


    I'm just modifying Dwain's query to reduce the code by using dense_rank.

    WITH SampleData (ID, Data) AS

    (

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P1' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P2' UNION ALL

    SELECT 'A', 'P3' UNION ALL

    SELECT 'A', 'P4' UNION ALL

    SELECT 'B', 'P2' UNION ALL

    SELECT 'C', 'P3' UNION ALL

    SELECT 'C', 'P4' UNION ALL

    SELECT 'D', 'P3' UNION ALL

    SELECT 'D', 'P5'

    )

    SELECT ID

    ,D1=MAX(CASE rn WHEN 1 THEN Data END)

    ,D2=MAX(CASE rn WHEN 2 THEN Data END)

    ,D3=MAX(CASE rn WHEN 3 THEN Data END)

    ,D4=MAX(CASE rn WHEN 4 THEN Data END)

    ,D5=MAX(CASE rn WHEN 5 THEN Data END)

    FROM

    (

    SELECT ID, Data, rn=DENSE_RANK() OVER (PARTITION BY ID ORDER BY Data)

    FROM SampleData a

    ) a

    GROUP BY ID;

    +1 - DENSE_RANK didn't even come to mind for some reason.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

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

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