How to use a subquery to insert comma separated values into a temporary table

Question

I'm trying to populate the Employees field in the temporary table using a subquery that comma separates the employee ids. My syntax is not correct.

What is the correct syntax to get the comma separated employee ids to insert into the temporary table? Here is my code:

declare @CommaSeperatedList varchar(max)

create table #tmp
(
    ManagerId int,
    Employees varchar(max)
)

insert into #tmp (ManagerId, Employees)
    select 
        m.Id, 
        select @CommaSeperatedList = COALESCE(@CommaSeperatedList + ', ', '') + cast(emp.Id as varchar from emp  select @CommaSeperatedList 
    from Manager m
inner join Employee emp on
    m.EmployeeId = emp.Id

Jason A. Long · Accepted Answer · 2017-08-20T07:55:14.960

2

Unless you are using SQL Azure or SQL Server 2017, which have the new STRING_AGG function, the best option is to use the FOR XML PATH('') method.

It looks like the following...

SELECT 
    e1.mgrid,
    Employees = STUFF((
                SELECT
                    CONCAT(', ', e2.empid)
                FROM 
                    HR.Employees e2
                WHERE 
                    e1.mgrid = e2.mgrid
                FOR XML PATH('')
                ), 1, 2, '')
FROM 
    HR.Employees e1
WHERE 
    e1.mgrid IS NOT NULL 
GROUP BY
    e1.mgrid;

HTH, Jason

edited Aug 20 '17 at 07:55

answered Aug 20 '17 at 00:40

Jason A. Long

4,382
1
12
17

No problem. Glad to help. :) – Jason A. Long Aug 21 '17 at 19:37
Also, check out this helpful post on stackoverflow to understand the syntax for STUFF and FOR XML PATH - it helped me grasp it: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server – Frekster Aug 21 '17 at 19:40

score 0 · Answer 2 · answered Aug 21 '17 at 10:39

You can use these examples on how to concat your rows. Be carefull of using XML PATH - It cant kill your CPU Performance. But if it isnt too many rows, i would go for XML PATH.

Look for more options here https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Example could be like this:

SELECT ',' + SUB.Name AS [text()] FROM Production.ProductSubcategory SUB WHERE SUB.ProductCategoryID = CAT.ProductCategoryID FOR XML PATH('') , 1, 1, '' ) AS [Sub Categories] FROM Production.ProductCategory CAT

How to use a subquery to insert comma separated values into a temporary table

2 Answers2