sql concatenate multiple values [duplicate]

Question

This question already has answers here:

How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field? [duplicate] (13 answers)

Closed 12 years ago.

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.

• John Woo: What RDBMS 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...
• user2264507: Using SQL Server 2008
• Gopesh Sharma: stackoverflow.com/questions/6899/… or stackoverflow.com/questions/194852/…

Answer 1

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.

• user2264507: 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
• TechDo: Add where condition on Column1. Please check the edited answer.
• user2264507: I only have the ONE table.
• user2264507: I got it to work, thanks techdo. But it was slightly confusing using t1 and t2 since I only have 1 table.
• TechDo: t1 and t2 are alias names given for your table.