CSV value in pivot query

Question

here is my table

Manger  EmpID
ABC     5
ABC     6
XYZ     2
XYZ     3

if i use pivot query i am getting below output.

 ABC    XYZ
 6      3

But actually i want such output.

  ABC   XYZ
  5,6   2,3

I dont' want aggregate value, i want csv values. can anyone please help me to get such output. here is my sql code

    Select * 
INTO #TEMP
FROM 
(SELECT 'ABC' As Manger , 5 as EmpID
union
SELECT 'ABC' As Manger , 6 as EmpID
union
SELECT 'XYZ' As Manger , 2 as EmpID
union
SELECT 'XYZ' As Manger , 3 as EmpID
) A

select * 
FROM #TEMP



select * 
FROM #TEMP
PIVOT
(
    MAX([EmpID])
    FOR Manger IN ([ABC],[XYZ])
) as P

drop table #TEMP

Answer 1

Try this:

SELECT * 
FROM (
   SELECT DISTINCT Manager, 
          STUFF((SELECT ',' + CAST(EmpID AS VARCHAR(MAX)) AS [text()]
                 FROM #TEMP AS t2
                 WHERE t1.Manager = t2.Manager
                 FOR XML PATH('')), 1, 1, '') AS EmpID
   FROM #TEMP AS t1) AS SRC       
PIVOT
(
    MAX([EmpID])
    FOR Manager IN ([ABC],[XYZ])
) as P

The trick is to pre-process your table before applying PIVOT. The above query uses FOR XML PATH to create a comma separated list of EmpID values for each Manager.

Demo here