I have found examples for concatenating multiple rows of a single column, but I have been having difficulties in applying it for two columns or more. Here is example of the table:
ID Col1 Col2 Col3
1 1A 2A 1
2 1B 2B 2
2 1C 2C 3
3 1D 2D 4
3 1E 2E 5
Desired result:
ID Col1 Col2 Col3
1 1A 2A AGGREGATE such as MIN
2 1B,1C 2B,2C "
3 1D,1E 2D,2E "
How could I accomplished the above? Thanks.
You can use XML PATH to achieve this
Sample data
DECLARE @tbl TABLE(ID INT,Col1 VARCHAR(5),Col2 VARCHAR(5),Col3 INT)
INSERT @tbl
SELECT 1, '1A', '2A', 1 UNION
SELECT 2, '1B', '2B', 2 UNION
SELECT 2, '1C', '2C', 3 UNION
SELECT 3, '1D', '2D', 4 UNION
SELECT 3, '1E', '2E', 5
Using XML PATH
SELECT tbl.ID
,LEFT(tbl.Col1, LEN(tbl.Col1) - 1) AS Col1
,LEFT(tbl.Col2, LEN(tbl.Col2) - 1) AS Col2
,Col3
FROM(SELECT DISTINCT sub2.ID
,(SELECT sub.Col1 + ',' AS [text()]
FROM @tbl AS sub
WHERE sub.ID = sub2.ID
ORDER BY sub.ID
FOR XML PATH ('')
) AS Col1
,(SELECT sub.Col2 + ',' AS [text()]
FROM @tbl AS sub
WHERE sub.ID = sub2.ID
ORDER BY sub.ID
FOR XML PATH ('')
) AS Col2
,(SELECT MIN(Col3)
FROM @tbl AS sub
WHERE sub.ID = sub2.ID
GROUP BY ID
) AS Col3
FROM @tbl sub2
) AS tbl
Output
ID Col1 Col2 Col3
1 1A 2A 1
2 1B,1C 2B,2C 2
3 1D,1E 2D,2E 4
Try this,
SELECT DISTINCT A.ID,
Stuff((SELECT DISTINCT ',' + A1.COL_1
FROM #YOUR_TABLE A1
WHERE A.ID = A1.ID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') TEMP_1,
Stuff((SELECT DISTINCT ',' + A1.COL_2
FROM #YOUR_TABLE A1
WHERE A.ID = A1.ID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') TEMP_2,
(SELECT Min(COL_3)
FROM #YOUR_TABLE A1
WHERE A.ID = A1.ID
GROUP BY ID) AS COL_3
FROM #YOUR_TABLE A;
WITHtechnet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx