-1

Say I have a table with ProductID and OrderNumber. There are multiple products each with multiple orders.

What I'm trying to do is turn: 

+-----------+-------------+
| ProductID | OrderNumber |
+-----------+-------------+
|         1 |           1 |
|         1 |           2 |
|         1 |           3 |
|         2 |           4 |
|         2 |           5 |
|         3 |           6 |
|         3 |           7 |
+-----------+-------------+

into:

+-----------+-------------+
| ProductID | OrderNumber |
+-----------+-------------+
|         1 |     1, 2, 3 |
|         2 |        4, 5 |
|         3 |        6, 7 |
+-----------+-------------+

It's easy enough to create a comma separated list of all the order numbers like shown here: Convert multiple rows into one with comma as separator.

What I have not been able to figure out is how to create the array from rows while still keeping the ProductID information intact.

sastrup
  • 189
  • 1
  • 11
  • If I understand correctly (a sample of what you are looking for would help), a dynamic pivot might be what you want. Add a sample of the array output you desire. I might be able to answer. – kalaolani Mar 02 '18 at 18:51
  • Aren't both of those in the post above? That first output is the table currently and the second is the array output desired. – sastrup Mar 02 '18 at 18:53
  • I see... I thought the author had that already and wanted to make a different array. – kalaolani Mar 02 '18 at 18:54
  • You can do this with a dynamic PIVOT. – kalaolani Mar 02 '18 at 18:57
  • 3
    This question has been asked dozens of times – n8. Mar 02 '18 at 19:01
  • @n8 I apologize if this has been asked & answered previously. I did search prior to posting and found the linked article but had not seen a question specifically about keeping categories intact when creating the array. – sastrup Mar 02 '18 at 19:08
  • Sometimes you just need to learn how to phrase a question. – n8. Mar 05 '18 at 16:56
  • In any case, it's good you have an answer! – n8. Mar 05 '18 at 16:57

1 Answers1

3

U need to use like below-

SELECT b.ProductID 
    , STUFF 
        ((
        SELECT CONCAT(', ' , a.OrderNumber)
        FROM PO a
        WHERE ( a.ProductID = b.ProductID )
        FOR XML PATH('')
        ) ,1,2,'') 
        AS OrderNumber
FROM PO b
GROUP BY b.ProductID

OUTPUT

ProductID   OrderNumber
----------- -------------------
1           1, 2, 3
2           4, 5
3           6, 7

(3 rows affected)

For more details pls refer -

https://msbiskills.com/2017/12/25/sql-puzzle-dynamic-pivot-puzzle/

or

https://msbiskills.com/2015/03/25/t-sql-query-group-by-xml-path-puzzle/

Pawan Kumar
  • 1,991
  • 10
  • 12