SQL Server Stuff and comma delimited column

Question: I have two tables and would like to return a recordset which is made up of table1 plus an extra column which is made up of a comma-delimited string from table2 based on an INNER JOIN.

I’ve read the comma delimited article , but this returns the full recordset as a CSV.SQL Server – Create a comma delimited sql result set (SQL Server ...

Answer: One option to use is the SQL Server STUFF function.The SQL Server STUFF function is used to insert one string into another string. This makes it useful for what you’re attempting to achieve.

You can see from the example how one of the SELECT columns is a STUFF function which generates a recordset from table 2 .

In this example , there are two tables. The SELECT statement returns all the rows from #temp1 and a comma delimited from #temp2 , based on a JOIN between the tables

CREATE TABLE #temp1 
(musician_ID INT,
musician_name nvarchar(20))

CREATE TABLE #temp2
(musician_ID INT,
instrument nvarchar(20))

INSERT INTO #temp1
select 1,'Prince'
UNION
select 2,'Santana'

INSERT INTO #temp2
select 1,'Guitar'
UNION
select 1,'Drums'
UNION
select 2,'Guitar'

select musician_name,
STUFF(
         (SELECT ', ' + convert(varchar(20), t2.instrument, 120)
          FROM #temp2 t2
          where t1.musician_ID = t2.musician_ID
          FOR XML PATH (''))
          , 1, 1, '')  AS instruments

from #temp1 t1

DROP TABLE #temp1
DROP TABLE #temp2

musician_name instruments
Prince  Drums, Guitar
Santana  Guitar

This sort of solution is useful n reporting , but if you're relying on this solution for an OLTP type site - you may want to consider the database design and impact on performance

Read More

Where to maintain data integrity rules? (SQL Server DBA)

SQL Server architecting write-intensive databases (SQL Server DBA)

What is First Normal Form, Second Normal and Third Normal Form ...