How to select a list of rows as a single, concatenated row?

Transact-SQL
1

I need to transfer data from a list of items into a single cell, grouped by the ProductID. My query below gives me the data, but I am not sure how to concatenate separate rows while grouping them by ProductID.

It might sound like an obvious one, but it's got me stumped. Even a tutorial or pointing me to the right function would help. Thanks!

CREATE TABLE #ProductDetails ( ID INT, ProductID NVARCHAR( 50 ), Trait  NVARCHAR( 500 ) );

INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('1431','1082081','Front Lens Cap')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('1432','1082081','Rear Lens Cap')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('1433','1082081','Pouch')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('1933','1081808','Wide Camera Strap')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('1934','1081808','Battery Cover')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('1935','1081808','Software CD-ROM')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3225','1080989','Super Mario 3D World Game')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3226','1080989','Nintendo Land Game')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3227','1080989','Wii U GamePad')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3228','1080989','Wii U AC Adapter')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3229','1080989','Wii U GamePad AC Adapter')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3230','1080989','High-Speed HDMI Cable')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3231','1080989','Sensor Bar')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3232','1080989','Wii U Console Stand')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3233','1080989','Wii U GamePad Cradle')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('3234','1080989','Wii U GamePad Stand Support')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7623','1078549','Wireless Subwoofer')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7624','1078549','Remote Control')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7625','1078549','3 x AAA Batteries')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7635','1078517','iLife (includes iPhoto, iMovie and GarageBand)')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7636','1078517','iWork (includes Pages, Numbers and Keynote)')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7637','1078517','Power Cord')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7638','1078517','AC Wall Plug')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('7639','1078517','MagSafe 2 Power Adapter')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('8818','1078007','Skeleton Backdoor')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('8819','1078007','Curved Adhesive Mount')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('8820','1078007','Flat Adhesive Mount')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('8821','1078007','Quick Release Buckle')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('8822','1078007','3-Way Pivot Arm')
INSERT INTO #ProductDetails (ID, ProductID, Trait) VALUES ('8823','1078007','USB Cable')

SELECT * FROM #ProductDetails
2

What is the column you are trying to concatenate? Assuming it is the Trait column:

SELECT
	a.ProductId,
	STUFF(b.Traits,1,1,'') AS Traits
FROM
	(SELECT DISTINCT ProductId FROM #ProductDetails)  a
	CROSS APPLY
	(
		SELECT ',' + Trait
		FROM #ProductDetails b
		WHERE a.ProductID = b.ProductID
		ORDER BY b.ID
		FOR XML PATH('')
	) b(Traits);
3

Perfect, that's exactly what I needed.

It looks like the next lesson for me is to learn about Cross Apply.

Thank you