0

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
RJV
  • 287
  • 1
  • 7
  • 20
  • 1
    SELECT ID, Emp_ID, Dept = STUFF((SELECT ', ' + Dept FROM EmployeeNew AS e2 WHERE e2.Emp_ID = e1.Emp_ID ORDER BY Dept FOR XML PATH('')), 1, 1, ''), Role_ID FROM EmployeeNew AS e1 GROUP BY Emp_ID,ID,Role_ID ORDER BY Emp_ID – SURJEET SINGH Bisht Nov 14 '16 at 07:02

2 Answers2

3
    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'
Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
Mansoor
  • 4,061
  • 1
  • 17
  • 27
0

Try this:

SELECT ID, Emp_ID, Dept = STUFF((SELECT ', ' + Dept 
  FROM EmpTBL AS e2
   WHERE e2.Emp_ID = e1.Emp_ID 
   ORDER BY Dept
   FOR XML PATH('')), 1, 1, ''), Role_ID, Status
FROM EmpTBL AS e1
GROUP BY Emp_ID
ORDER BY Emp_ID
Mohammad Nurul Huda Rimon
  • 2,475
  • 1
  • 14
  • 18