none
How to concatinate items having same Id using STUFF and FOR XML PATH

    Question

  • SQL SERVER 2008 R2, I have three tables as follows:

    (1) OrderProductVariant
    Id ProductVariantId
    ----------------------------------------
    1 | 22
    2 | 23
    3 | 24
    4 | 25

    (2) ProductVariant
    Id ProductId
    ----------------------------------------
    22 | 34
    22 | 35
    23 | 36
    23 | 37
    24 | 38
    24 | 39

    (3) Product
    Id Product
    ----------------------------------------
    34 | KBDMouse800 
    35 | KBDMK250
    36 | LaptopCorei7
    37 | LaptopCorei5
    38 | BluetoothMouse1000
    39 | PresentorR800

    I want the output result to be :

    OrderProductVariant.Id | Product
    -----------------------------------------
    1   | KBDMouse800, KBDMK250
    2   | LaptopCorei7, LaptopCorei5
    3   | BluetoothMouse1000, PresentorR800

    Sunday, March 17, 2013 8:54 AM

Answers

  • SELECT      (A.ID ) , STUFF((    SELECT ',' + C.Product AS [text()]
                            FROM Product C, ProductVariant B 
                            WHERE
                            C.ID = B.ProductId and A.ProductVariantId = B.Id
                            FOR XML PATH('') 
                            ), 1, 1, '' )
         FROM  OrderProductVariant A 



    Hope it Helps!!



    Sunday, March 17, 2013 9:14 AM
  • create table #OrderProductVariant (id int,ProductVariantId int)
    insert into #OrderProductVariant values (1,22),(2,23),(3,24),(4,25)

    create table #ProductVariant (id int,ProductId int)
    insert into #ProductVariant 
    values (22,34),(22,35),(23,36),(23,37),(24,38),(24,39)


    create table #Product (id int,Product varchar(50))
    insert into #Product 
    values (34,'KBDMouse800'),(35,'KBDMK250'),
    (36,'LaptopCorei7'),(37,'LaptopCorei5'),
    (38,'BluetoothMouse1000'),(39,'PresentorR800')




    with cte
    as
    (
    select o.id,product from #OrderProductVariant o
    join #ProductVariant pv on o.ProductVariantId=
    pv.id join #Product p on p.Id=pv.ProductId
    ) SELECT m1.id,
           ( SELECT m2.product + ','
               FROM cte m2
              WHERE m2.id = m1.id
              ORDER BY product
                FOR XML PATH('') ) AS token
      FROM cte m1
     GROUP BY m1.id ;

     ----oprion two
    with cte
    as
    (
    select o.id,product from #OrderProductVariant o
    join #ProductVariant pv on o.ProductVariantId=
    pv.id join #Product p on p.Id=pv.ProductId
    ) SELECT DISTINCT
            id,
            STUFF(group_list, 1, 1, '') AS groups
    FROM cte AS A
    CROSS APPLY (SELECT ',' + [Product]
                  FROM cte AS B
                  WHERE B.ID = A.ID
                  FOR XML PATH('')) AS T(group_list);

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, March 17, 2013 9:16 AM
    Answerer

All replies

  • SELECT      (A.ID ) , STUFF((    SELECT ',' + C.Product AS [text()]
                            FROM Product C, ProductVariant B 
                            WHERE
                            C.ID = B.ProductId and A.ProductVariantId = B.Id
                            FOR XML PATH('') 
                            ), 1, 1, '' )
         FROM  OrderProductVariant A 



    Hope it Helps!!



    Sunday, March 17, 2013 9:14 AM
  • create table #OrderProductVariant (id int,ProductVariantId int)
    insert into #OrderProductVariant values (1,22),(2,23),(3,24),(4,25)

    create table #ProductVariant (id int,ProductId int)
    insert into #ProductVariant 
    values (22,34),(22,35),(23,36),(23,37),(24,38),(24,39)


    create table #Product (id int,Product varchar(50))
    insert into #Product 
    values (34,'KBDMouse800'),(35,'KBDMK250'),
    (36,'LaptopCorei7'),(37,'LaptopCorei5'),
    (38,'BluetoothMouse1000'),(39,'PresentorR800')




    with cte
    as
    (
    select o.id,product from #OrderProductVariant o
    join #ProductVariant pv on o.ProductVariantId=
    pv.id join #Product p on p.Id=pv.ProductId
    ) SELECT m1.id,
           ( SELECT m2.product + ','
               FROM cte m2
              WHERE m2.id = m1.id
              ORDER BY product
                FOR XML PATH('') ) AS token
      FROM cte m1
     GROUP BY m1.id ;

     ----oprion two
    with cte
    as
    (
    select o.id,product from #OrderProductVariant o
    join #ProductVariant pv on o.ProductVariantId=
    pv.id join #Product p on p.Id=pv.ProductId
    ) SELECT DISTINCT
            id,
            STUFF(group_list, 1, 1, '') AS groups
    FROM cte AS A
    CROSS APPLY (SELECT ',' + [Product]
                  FROM cte AS B
                  WHERE B.ID = A.ID
                  FOR XML PATH('')) AS T(group_list);

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, March 17, 2013 9:16 AM
    Answerer
  • The following blog page has examples for delimited string list creation:

    http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/


    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Sunday, March 17, 2013 12:20 PM
    Moderator