SQL Puzzle: SQL Advance Query - Print comma separated Divisions

SQL Puzzle: SQL Advance Query – Print comma separated Divisions

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:

divids divisionname

----------- -------------------------

1 Animation

2 Database

3 Production

4 Tester

Input data for Employee Table:

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:

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:

CREATE TABLE tbl_emp

(

empid INT

,empname VARCHAR(10)

,divids VARCHAR(10)

)

CREATE TABLE tbl_divisions

(

divids INT

,divisionname VARCHAR(25)

)

Insert Into tbl_divisions Values

(1,'Animation'),(2,'Database'),(3,'Production'),(4,'Tester')

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')

Solution:

;WITH CTE AS

(

SELECT empid,empname,divids FROM

(

SELECT empid,empname,CAST((''+replace(divids,',' ,'')+'') AS XML) xmlcol

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...

Oct 7, 2017Anvesh Patel

Comments: 2

Dinesh I.S

October 10, 2017 at 12:51 pm

—Method-1
;
WITH CTE
As
(
Select A.EmpID,empname,B.Divids From (
Select EMpID,empName,Cast(”+Replace(Divids,’,’,”)+” As XML) As XMLData
From TBL_EMP) A
Outer Apply (Select r.value(‘.’,’NvarChar(max)’) As Divids
From XMLData.nodes(‘t’) As Records(r)
) B
)
Select EmpID,EmpName,(Select Convert(VarChar(500),DivisionName)+’,’ From CTE C Inner JOin TBL_Divisions D On D.Divids=C.Divids Where C.EmpID=A.EmpID For XML PATH(”)) As Divisions
From TBL_Emp A

Reply Cancel

Dinesh I.S

October 10, 2017 at 12:52 pm

–Method-2
Declare @DivisionsDetails Table(EmpID Int,DividsID Int)
Declare @EmpID Int;
Select @EmpID=Min(EmpID) From TBL_Emp
Declare @Len As Int;
Declare @Divids VarChar(4000)
While @EmpID Is Not Null
Begin

Select @Divids=Divids From TBL_Emp Where EmpID=@EmpID

While Len(@Divids)>0
Begin
Set @Len=(Case CharIndex(‘,’,@Divids) When 0 Then Len(@Divids) Else CharIndex(‘,’,@Divids)-1 End)
Insert Into @DivisionsDetails
Values(@EmpID,SubString(@Divids,1,@Len))

Set @Divids=(Case Len(@Divids)-@Len When 0 Then Null Else Right(@Divids,Len(@Divids)-@Len-1) End)
End

Select @EmpID=Min(EmpID) From TBL_Emp Where EmpID>@EmpID
End

Select EmpID,EmpName,(Select Convert(VarChar(500),DivisionName)+’,’ From TBL_Divisions D Inner JOin @DivisionsDetails DL On DL.DividsID=D.Divids Where DL.EmpID=E.EmpID For XML PATH(”)) As DivisionName
From TBL_EMP E

Reply Cancel

SQL Puzzle: SQL Advance Query – Print comma separated Divisions

Leave a Reply to Dinesh I.S Cancel reply

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !