You can still use the PIVOT operator. Or the old-school way of doing a pivot... either works. BTW, note how I put your data into code that inserts the test data into a table. You really should do this for us in the future.

DECLARE @test-2 TABLE (ID INTEGER, Col CHAR(4), seq INTEGER, strdata VARCHAR(50));

INSERT INTO @test-2 (ID, Col, seq, strdata)

VALUES (1, 'Col1', 1, 'I' ),

(1, 'Col1', 2, 'Love' ),

(1, 'Col1', 3, 'SQLservercentral' ),

(1, 'Col2', 1, 'sql' ),

(1, 'Col2', 2, 'server' ),

(1, 'Col2', 3, 'centrl' ),

(1, 'Col4', 1, 'Provides v' ),

(1, 'Col4', 2, 'ery Helpfull' ),

(1, 'Col6', 1, 'Ser' ),

(1, 'Col6', 2, 'vice.' ),

(2, 'Col1', 1, 'Thank You' ),

(2, 'Col1', 2, ',So much.' ),

(2, 'Col1', 3, 'For ' ),

(2, 'Col3', 1, 'All your' ),

(2, 'Col3', 2, 'Helps' ),

(2, 'Col4', 1, 'Appreciated,' ),

(2, 'Col4', 2, 'Thanks. Thanks. Thanks.' );

-- NEW SCHOOL: Use the PIVOT operator

WITH cte AS

(

-- for each ID/Col, get the strdata concatenated together

SELECT t1.ID,

t1.Col,

MAX(ca.x) x

FROM @test-2 t1

-- See http://www.sqlservercentral.com/articles/comma+separated+list/71700/ for how this FOR XML syntax works

CROSS APPLY (SELECT (SELECT strdata + ''

FROM @test-2 t2

WHERE t2.ID = t1.ID

AND t2.Col = t1.Col

ORDER BY t2.seq

FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

) ca(x)

GROUP BY t1.ID, t1.Col

)

SELECT ID, [Col1], [Col2], [Col3], [Col4], [Col5], [Col6]

FROM cte

PIVOT (MAX(x) FOR Col IN ([Col1], [Col2], [Col3], [Col4], [Col5], [Col6])) AS PivotTable;

-- OLD SCHOOL:

WITH cte AS

(

-- for each ID/Col, get the strdata concatenated together

SELECT t1.ID,

t1.Col,

MAX(ca.x) x

FROM @test-2 t1

-- See http://www.sqlservercentral.com/articles/comma+separated+list/71700/ for how this FOR XML syntax works

CROSS APPLY (SELECT (SELECT strdata + ''

FROM @test-2 t2

WHERE t2.ID = t1.ID

AND t2.Col = t1.Col

ORDER BY t2.seq

FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

) ca(x)

GROUP BY t1.ID, t1.Col

)

SELECT ID

,Col1 = MAX(CASE WHEN Col = 'Col1' THEN x ELSE NULL END)

,Col2 = MAX(CASE WHEN Col = 'Col2' THEN x ELSE NULL END)

,Col3 = MAX(CASE WHEN Col = 'Col3' THEN x ELSE NULL END)

,Col4 = MAX(CASE WHEN Col = 'Col4' THEN x ELSE NULL END)

,Col5 = MAX(CASE WHEN Col = 'Col5' THEN x ELSE NULL END)

,Col6 = MAX(CASE WHEN Col = 'Col6' THEN x ELSE NULL END)

FROM cte

GROUP BY ID;

Both of these methods return the same result set:

ID Col1 Col2 Col3 Col4 Col5 Col6

-- --------------------- --------------- ------------- ----------------------------------- ---- --------

1 ILoveSQLservercentral sqlservercentrl NULL Provides very Helpfull NULL Service.

2 Thank You,So much.For NULL All yourHelps Appreciated,Thanks. Thanks. Thanks. NULL NULL