SQLSERVER Combine multiple lines into 1 row

Question

I have the following tables:

Product_Order
Order_Num   Order_Date
1           10/12/2017
2           10/31/2017
3           11/01/2017

Product_Order_Dtl
Order_Num   Product_Desc
1           Toy_01
1           Toy_02
1           Toy_03
2           Toy_01
2           Toy_05
3           Toy_01

I am trying to update the Product Order Table to list all the products associated with that order into a new column called Product_List. Just like the following:

Product_Order
Order_Num   Order_Date  Product_List
1           10/12/2017  Toy_01, Toy_02, Toy_03
2           10/31/2017  Toy_01, Toy_05
3           11/01/2017  Toy_01

Is there a way to do that using an update statement? I am using a version of SQL 2012.

Possible duplicate of [How to make a query with group\_concat in sql server](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — Juan Carlos Oropeza, Nov 22 '17 at 18:03
Please carefully consider @WEI_DBA's question before using one of the answers that have been offered! Unless you have a compelling reason, this is almost certainly a bad idea. — Joe Farrell, Nov 22 '17 at 18:12

Hasmukh Viradiya · Accepted Answer · 2017-11-22T18:20:07.393

3

you can use below query

SELECT A.Order_Num, A.Order_Date, STUFF((SELECT ',' + Product_Desc
          FROM Product_Order_Dtl C
where C.Order_Num=A.Order_Num            
          FOR XML PATH('')), 1, 1, '') AS Product_List
from Product_Order A
inner join Product_Order_Dtl B on A.Order_Num =B.Order_Num 
group by A.Order_Num,A.Order_Date

edited Nov 22 '17 at 18:20

answered Nov 22 '17 at 18:08

Hasmukh Viradiya

174
8

Chris Mack · Answer 2 · 2017-11-22T18:53:35.500

0

This will do it:

SELECT
    PO.Order_Num
    , PO.Order_Date
    ,
        STUFF
        (
            (
                SELECT ', ' + POD.Product_Desc
                FROM Product_Order_Dtl POD
                WHERE POD.Order_Num = PO.Order_Num
                ORDER BY POD.Product_Desc
                FOR XML PATH ('')
            ), 1, 2, ''
        ) Product_List
FROM Product_Order PO

edited Nov 22 '17 at 18:53

answered Nov 22 '17 at 18:03

Chris Mack

5,148
2
12
29

SQLSERVER Combine multiple lines into 1 row

2 Answers2