Sum up articles per orderID via SQL Server

Question

I have the following:

OrderID | Articlenumber|
--------+--------------+
1       | 123          |
2       | 222          |
1       | 799          |
1       | 987          |
2       | 444          |
3       | 212          |
2       | 222          |
1       | 898          |

and I want the following (sum up all article numbers per orderID):

orderID|articelnumber       |
-------+--------------------+
1      |123, 799, 987, 898  |
2      |222, 444, 222       |
3      |212                 |

or:

orderID|articelnumber|articelnumber|articelnumber |articelnumber |
-------+-------------+-------------+--------------+--------------+
1      |123          |799          |987           | 898          |
2      |222          |444          |222           |              |
3      |212          |             |              |              |

How can I do it with SQL-Server? The number of articles per orderID is variable.

Thanks a lot!

Well there are two different things there. The first is creating a delimited list and the second is a pivot. — Sean Lange, Jul 02 '18 at 14:59
I suspect it'll need to be a dynamic pivot as well. Also, I strongly suggest against having multiple columns with the same name. You'll quickly find it'll bite you in the behind. — Thom A, Jul 02 '18 at 15:05
To do that, you need to use the STUFF function. Here is an example https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/ — Fábio Nascimento, Jul 02 '18 at 15:24
Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Benjamin Cuningham, Jul 02 '18 at 15:25

score 1 · Answer 1 · answered Jul 02 '18 at 15:52

Is something like this what you want?

;WITH cte AS
(
    SELECT OrderID, Articlenumber
    FROM [YOUR_TABLE]
)
SELECT 
    OrderID,
    STUFF((SELECT ',' + Articlenumber FROM [YOUR_TABLE] WHERE [YOUR_TABLE].OrderID = cte.OrderID FOR XML PATH('')), 1, 1, '') articelnumber
FROM cte
GROUP BY OrderID
ORDER BY 1

Sum up articles per orderID via SQL Server

1 Answers1