Hi,

I had to come back again......

I used below code, only change in what has been suggested earlier is addition of an additional record so that I get duplicate values for 5, Germany, Berlin.

DECLARE @t TABLE( ID INT, ColA nvarCHAR(max), ColB nvarCHAR(max), ColC nVarCHAR(max) )

INSERT INTO @t(ID, ColA, ColB, ColC )

VALUES

( 1, 'India', 'VP', 'Mumbai' ),

( 2, 'Aus', 'Consultant', 'Sydney' ),

( 2, 'Aus', 'VP', 'Melbourne' ),

( 3, 'France', 'Consultant', 'Paris' ),

( 4, 'Swiss', 'SC', 'Zurich' ),

( 4, 'Swiss', 'Consultant', 'Geneva' ),

( 4, 'Swiss', 'VP', 'Lusanne' ),

( 5, 'Germany', 'Consultant', 'Koln' ),

( 5, 'Germany', 'SC', 'Berlin' ),

( 5, 'Germany', 'VP', 'Berlin' );

SELECT a.ID,

a.ColA,

STUFF((SELECT ',' + b.ColB AS "text()"

FROM @t b

WHERE b.ID = a.ID

AND b.ColA = a.ColA

ORDER BY b.ColB

FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColB,

STUFF((SELECT ',' + b.ColC AS "text()"

FROM @t b

WHERE b.ID = a.ID

AND b.ColA = a.ColA

ORDER BY b.ColC

FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ColC

FROM @t a

GROUP BY a.ID,a.ColA

ORDER BY a.ID,a.ColA;

In output, I want, for last record for column C, to show Berlin Once and twice.

Any suggestions around that one ? As I would have many multiple values in my actual tables and imagine Berlin repeating 5-6 times would look weird.

Last record for column C should be

"Berlin, Koln" and not

"Berlin, Berlin, Koln"

Thanks in advance.