concatenate row values into single row

Question

concatenate row values into single row

Dyn-o-mite!!

Valued Member

Points: 67
More actions
December 13, 2011 at 7:57 am

#248106

hello, can anyone please show me how to concatenate values from multiple rows to single row ??
for e.g
create table test_table
(
letter varchar(max),
word varchar(max)
);
insert into test_table
values
('A','apple'),
('A','aeroplane'),
('C','car'),
('D','dairy'),
('C','camel');
the output should be as follows
letter words
----- ---------------
A apple,aeroplane
C car,camel
D dairy
I would greatly appreciate your help.

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Dyn-o-mite!! (12/13/2011)
hello, can anyone please show me how to concatenate values from multiple rows to single row ??
for e.g
create table test_table
(
letter varchar(max),
word varchar(max)
);
insert into test_table
values
('A','apple'),
('A','aeroplane'),
('C','car'),
('D','dairy'),
('C','camel');
the output should be as follows
letter words
----- ---------------
A apple,aeroplane
C car,camel
D dairy
I would greatly appreciate your help.

SELECT letter, STUFF((SELECT ',' + word

FROM test_table t2

WHERE t2.letter = t1.letter

ORDER BY word

FOR XML PATH('')), 1, 1, '') AS words

FROM test_table t1

GROUP BY letter

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dyn-o-mite!! Valued Member Points: 67 More actions · Answer 2

Dyn-o-mite!!

Valued Member

Points: 67

December 13, 2011 at 8:06 am

#1420639

thank you very much !!!