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
Prince Drums, 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