sql concatenate multiple values

Question

I have designed a view in SQL Server 2008 that displays 2 columns of distinct data in this format:

Column1 Column2
A       Text1
B       Text2
B       Text3
C       Text4
D       Text5
D       Text6
D       Text7
E       Text8

What do I have to do to get the view to display only the distinct Column1 values with Column2 values concatenated like this:

Column1 Column2
A       Text1
B       Text2, Text3
C       Text4
D       Text5, Text6, Text7
E       Text8

If I use the code suggested in the answers you seem to be considering as duplicate to this question, then I get this kind of result:

Column1 Column2
A       Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8
B       Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8
C       Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8
D       Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8
E       Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8

Edit: The revised code from @techdo worked, many thanks.

What [RDBMS](http://en.wikipedia.org/wiki/Relational_database_management_system) you are using? `RDBMS` stands for *Relational Database Management System*. `RDBMS is the basis for SQL`, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc... — John Woo, Apr 10 '13 at 04:54
http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su or http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Gopesh Sharma, Apr 10 '13 at 05:03

score 2 · Accepted Answer · edited May 23 '17 at 11:43

2

Please try:

SELECT
    Column1,
    STUFF(
    (SELECT ',' + Column2
       FROM YourTable t2
        WHERE t2.Column1=t1.Column1
        FOR XML PATH(''),type).value('.','nvarchar(max)'), 1, 1, '') AS Column2
FROM
    YourTable t1
GROUP BY Column1

Check LINK for more details.

edited May 23 '17 at 11:43

Community

1
1

answered Apr 10 '13 at 05:03

TechDo

18,398
3
51
64

this code returns all column2 values for each column1 row like this: A Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8 B Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8 C Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8 – user2264507 Apr 10 '13 at 21:05
Add `where` condition on `Column1`. Please check the edited answer. – TechDo Apr 11 '13 at 04:42
I only have the ONE table. – user2264507 Apr 11 '13 at 21:19
I got it to work, thanks techdo. But it was slightly confusing using t1 and t2 since I only have 1 table. – user2264507 Apr 11 '13 at 21:25
`t1` and `t2` are alias names given for your table. – TechDo Apr 12 '13 at 03:38

sql concatenate multiple values

1 Answers1