SQL Server Stuff and comma delimited column

24 March,2017 by Jack Vamvas

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 ...

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Stuff and comma delimited column


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer