Pivot a row

Question

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26983 More actions · Answer 1

DECLARE @T TABLE(RowID INT, Col1 CHAR(2), Col2 CHAR(2), Col3 CHAR(2), ErrorCD VARCHAR(10))

INSERT INTO @T(RowID,Col1,Col2,Col3,ErrorCD)

SELECT 1, 'A1', 'B1', 'C1', 'E1' UNION ALL

SELECT 2, 'A1', 'B1', 'C1', 'E2' UNION ALL

SELECT 3, 'A1', 'B1', 'C1', 'E3' UNION ALL

SELECT 4, 'A2', 'B1', 'C1', 'E1' UNION ALL

SELECT 5, 'A2', 'B1', 'C1', 'E2' UNION ALL

SELECT 6, 'A2', 'B1', 'C1', 'E3' UNION ALL

SELECT 7, 'A3', 'B1', 'C1', 'E1' UNION ALL

SELECT 8, 'A3', 'B1', 'C1', 'E2' UNION ALL

SELECT 9, 'A3', 'B1', 'C1', 'E3';

SELECT T1.Col1,T1.Col2,T1.Col3,

STUFF((SELECT ','+T2.ErrorCD AS "text()"

FROM @T T2

WHERE T2.Col1=T1.Col1

AND T2.Col2=T1.Col2

AND T2.Col3=T1.Col3

ORDER BY T2.ErrorCD

FOR XML PATH('')

),1,1,'') AS ErrorCD

FROM @T T1

GROUP BY T1.Col1,T1.Col2,T1.Col3;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

suneel-210238 SSC Enthusiast Points: 193 More actions · Answer 2

SSC Enthusiast

Points: 193

June 16, 2011 at 4:51 am

Thank you. That helps!! 🙂