Moving data from rows into single delimited field

  • texpic

    SSCertifiable

    Points: 5882

    Does someone have a solution for this.  If there is more than one MyId1 it needs to be in a single row with a delimiter (comma).  I'm pretty sure there would never be more than 50 MyId1's for each MyId2.  Appreciate any suggestions.


    CREATE TABLE #Sample (MyId1 varchar(99), MyId2 int)
    INSERT INTO #Sample
    SELECT 111, 987 UNION ALL
    SELECT 222, 987 UNION ALL
    SELECT 333, 987 UNION ALL
    SELECT 111, 988 UNION ALL
    SELECT 222, 988 UNION ALL
    SELECT 111, 989 UNION ALL
    SELECT 222, 989

    /*
    Desired Output

    MyId2        MyId1
    987            111,222,333
    988            111,222
    989            111,222
    */

  • texpic

    SSCertifiable

    Points: 5882

    I thought I did this once before but could not find the solution.  Continued to search and found it.


    IF OBJECT_ID('tempdb..#FlattenIt') IS NOT NULL DROP TABLE #FlattenIt
    CREATE TABLE #FlattenIt (MyId1 varchar(99), MyId2 int, MyId3 int)
    INSERT INTO #FlattenIt
    SELECT 111, 987, 1 UNION ALL
    SELECT 222, 987, 1 UNION ALL
    SELECT 333, 987, 1 UNION ALL
    SELECT 111, 988, 1 UNION ALL
    SELECT 222, 988, 1 UNION ALL
    SELECT 111, 989, 1 UNION ALL
    SELECT 222, 989, 1

    SELECT * FROM #FlattenIt

    --flatten
    IF OBJECT_ID('tempdb..#FlattenIt2') IS NOT NULL DROP TABLE #FlattenIt2
    SELECT MyId2, MyId3,
    LEFT(C.Data_Values, LEN(C.Data_Values) - 1) as Flat
    INTO #FlattenIt2 FROM #FlattenIt A
    CROSS APPLY (SELECT CAST(MyId1 AS varchar) + ','
    FROM #FlattenIt B
    WHERE A.MyId2 = B.MyId2 AND A.MyId3 = B.MyId3
    FOR XML PATH('') ) C ( Data_Values )
    GROUP BY MyId2, MyId3, C.Data_Values

    SELECT * FROM #FlattenIt2

  • Conficker

    Default port

    Points: 1457

    SELECT s.MyId2,
    STUFF( (SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated   
    FROM #Sample AS s
    GROUP BY s.myid2


  • saravanatn

    SSCarpal Tunnel

    Points: 4530

    Conficker - Tuesday, April 3, 2018 3:13 AM

    SELECT s.MyId2,
    STUFF( (SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated   
    FROM #Sample AS s
    GROUP BY s.myid2


    Can you kindly explain us how do  group by  works without aggregate function?

    Saravanan

  • Conficker

    Default port

    Points: 1457

    saravanatn - Tuesday, April 3, 2018 5:03 AM

    Conficker - Tuesday, April 3, 2018 3:13 AM

    SELECT s.MyId2,
    STUFF( (SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated   
    FROM #Sample AS s
    GROUP BY s.myid2


    Can you kindly explain us how do  group by  works without aggregate function?

  • sgmunson

    SSC Guru

    Points: 110459

    saravanatn - Tuesday, April 3, 2018 5:03 AM

    Conficker - Tuesday, April 3, 2018 3:13 AM

    SELECT s.MyId2,
    STUFF( (SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated   
    FROM #Sample AS s
    GROUP BY s.myid2


    Can you kindly explain us how do  group by  works without aggregate function?

    The FOR XML PATH is doing a "sort of" aggregation, in that it is just concatenating all the values coming out of the query, and the STUFF function is then taking that concatenated string and removing the first comma.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • saravanatn

    SSCarpal Tunnel

    Points: 4530

    sgmunson - Tuesday, April 3, 2018 9:11 AM

    saravanatn - Tuesday, April 3, 2018 5:03 AM

    Conficker - Tuesday, April 3, 2018 3:13 AM

    SELECT s.MyId2,
    STUFF( (SELECT ',' + #Sample.MyId1 FROM #Sample WHERE s.MyId2 = #Sample.myid2 FOR XML PATH ('')), 1, 1, '' ) AS CommaSeparated   
    FROM #Sample AS s
    GROUP BY s.myid2


    Can you kindly explain us how do  group by  works without aggregate function?

    The FOR XML PATH is doing a "sort of" aggregation, in that it is just concatenating all the values coming out of the query, and the STUFF function is then taking that concatenated string and removing the first comma.

    Thanks Steve

    Saravanan

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

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