This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to print comma separated divisions for all the employees.
Input data for Division Table:
1 2 3 4 5 6 |
divids divisionname ----------- ------------------------- 1 Animation 2 Database 3 Production 4 Tester |
Input data for Employee Table:
1 2 3 4 5 6 7 8 |
empid empname divids ----------- ---------- ---------- 1 Anvesh 1,2 2 Roy 1,2,3 3 Martin 2,4 4 Jenny 3 5 Nupur 3,4 6 Rajesh 1,4 |
Expected Output:
1 2 3 4 5 6 7 8 |
empid empname RecruiterNames ----------- ---------- --------------------------------- 1 Anvesh Animation, Database 2 Roy Animation, Database, Production 3 Martin Database, Tester 4 Jenny Production 5 Nupur Production, Tester 6 Rajesh Animation, Tester |
Create sample tables:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE TABLE tbl_emp ( empid INT ,empname VARCHAR(10) ,divids VARCHAR(10) ) GO CREATE TABLE tbl_divisions ( divids INT ,divisionname VARCHAR(25) ) GO Insert Into tbl_divisions Values (1,'Animation'),(2,'Database'),(3,'Production'),(4,'Tester') GO Insert Into tbl_emp Values (1,'Anvesh','1,2') ,(2,'Roy','1,2,3') ,(3,'Martin','2,4') ,(4,'Jenny','3') ,(5,'Nupur','3,4') ,(6,'Rajesh','1,4') GO |
Solution:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
;WITH CTE AS ( SELECT empid,empname,divids FROM ( SELECT empid,empname,CAST((' FROM tbl_emp ) s OUTER APPLY ( SELECT ProjectData.D.value('.', 'VARCHAR(2)') divids FROM s.xmlcol.nodes('X') ProjectData(D) ) a ) ,CTE1 AS ( SELECT empid,empname,divisionname FROM CTE c INNER JOIN tbl_divisions r on r.divids = c.divids ) SELECT empid, empname, STUFF (( SELECT ', ' + a.divisionname FROM CTE1 a WHERE ( a.empid = b.empid ) FOR XML PATH('') ) ,1,2,'') AS RecruiterNames FROM CTE1 b GROUP BY empid, empname ORDER BY empid |
Please try the different solution for this puzzle and share it via comment...