RJV RJV - 25 days ago 8
SQL Question

Group by column in SQL

I have Sql table called EmpTBL , Where it consist of data as:

ID Emp_ID Dept Role_ID Status
82 E00004 D1 3 1
82 E00004 D2 3 1
83 E00034 E2 1 1
84 E00043 G1 2 1
84 E00043 G2 2 1
84 E00043 G3 2 1
76 E01053 A1 1 1


I need to write a query that groups up
Emp_ID
and display
Dept
as

ID Emp_ID Dept Role_ID Status
82 E00004 D1, D2 3 1
83 E00034 E2 1 1
84 E00043 G1, G2, G3 2 1
76 E01053 A1 1 1

Answer
    CREATE TABLE #EmpTbl(ID INT, Emp_ID VARCHAR(100), Dept VARCHAR(100),Role_ID INT, Status INT)

    INSERT INTO  #EmpTbl(ID , Emp_ID , Dept ,Role_ID , Status )
    SELECT 82,'E00004','D1',3,1 UNION ALL         
    SELECT 82,'E00004','D2',3,1 UNION ALL 
    SELECT 83,'E00034','E2',1,1 UNION ALL  
    SELECT 84,'E00043','G1',2,1 UNION ALL  
    SELECT 84,'E00043','G2',2,1 UNION ALL  
    SELECT 84,'E00043','G3',2,1 UNION ALL  
    SELECT 76,'E01053','A1',1,1 

    SELECT DISTINCT T1.ID,T1.Emp_ID,A.Dept,T1.Role_ID,T1.Status
    FROM #EmpTbl T1
    JOIN
    (
    SELECT Emp_ID ,STUFF((SELECT ',' + Dept FROM #EmpTbl T3 WHERE T3.Emp_ID = T2.Emp_ID 
    FOR XML PATH('')),1,1,'') Dept
    FROM #EmpTbl T2
    GROUP BY Emp_ID 
    ) A ON A.Emp_ID = T1.Emp_ID'