MS SQL Select (with grouping)

Transact-SQL
1

Dear all,

I have a simple question regarding SQL Select.

I have a table in this format:

ORDER PRODUCT
4711 Product A
4711 Product B
4712 Product C

Any Ideas what is to do to create this results:

ORDER PRODUCT
4711 Product A, Product B
4712 Product C

The Idea is to create a select, identify the same order ID and append all the products to them.

Thanks
Stefan

2

If you are on SQL 2017, you can use STRING_AGG which would work beautifully for your problem. Otherwise, use something like this:

SELECT
	t1.[Order],
	Product = STUFF(c.Products,1,1,'')
FROM
	(SELECT DISTINCT t.[Order] FROM YourTable t) AS t1
	CROSS APPLY
    (
		SELECT ',' + t2.Product
		FROM
			YourTable t2
		WHERE
			t1.[Order] = t2.[Order]
		ORDER BY
			t2.Product
		FOR XML PATH('')
	) c(Products)
ORDER BY
	t1.[Order];
3

select distinct orders ,
STUFF((Select ','+ products
from products T1
where T1.orders=T2.orders
FOR XML PATH('')),1,1,'') as products from products T2