Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Replace Ids with Name ( Comma Separated Puzzle )

Puzzle Statement

This puzzle was asked to me via gmail. Here you have to Replace Ids with Name. In Jobs table we have comma separated recruiter Ids , we have to use Recruiter table and replace these Ids with their respective names. Please check sample input and expected output.

Gmail Puzzle - Comma Separated Puzzle

Gmail Puzzle – Comma Separated Puzzle

Sample Input

RecruiterID RecruiterName
1 Rajesh
2 Anitha
3 Lokanath
4 Moni
Jobid JobName RecruiterID
1 HCL Jobs 1,3
2 IBM Jobs 1,4
3 Accenture Jobs 1,2,4
4 GBS Jobs 1,2,3
5 ABVS Jobs 1,3,4
6 JULM Jobs 1,3,4

Expected Output

JobId JobName RecruiterNames
1 HCL Jobs Rajesh, Lokanath
2 IBM Jobs Rajesh, Moni
3 Accenture Jobs Rajesh, Anitha, Moni
4 GBS Jobs Rajesh, Anitha, Lokanath
5 ABVS Jobs Rajesh, Lokanath, Moni
6 JULM Jobs Rajesh, Lokanath, Moni

Script

Use below script to create table and insert sample data into it.

--


Create Table Jobs
(Jobid Numeric(4),
JobName Varchar(25),
RecruiterID Varchar(25))

Create Table Recruiter
(RecruiterID Numeric(4),
RecruiterName Varchar(25))

                                                                                                                                                                     
Insert Into Jobs Values (1,'HCL Jobs','1,3')
Insert Into Jobs Values (2,'IBM Jobs','1,4')
Insert Into Jobs Values (3,'Accenture Jobs','1,2,4')
Insert Into Jobs Values (4,'GBS Jobs','1,2,3')
Insert Into Jobs Values (5,'ABVS Jobs','1,3,4')
Insert Into Jobs Values (6,'JULM Jobs','1,3,4')


Insert Into Recruiter Values (1,'Rajesh')
Insert Into Recruiter Values (2,'Anitha')
Insert Into Recruiter Values (3,'Lokanath')
Insert Into Recruiter Values (4,'Moni')

GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using XML & Cross APPLY

--

;WITH CTE AS
(
               SELECT JobId,JobName,RecruiterID FROM
               (
                              SELECT JobId,JobName,CAST(('<X>'+replace(RecruiterId,',' ,'</X><X>')+'</X>') AS XML) xmlcol  FROM Jobs
               ) s
               OUTER APPLY
               (
                              SELECT ProjectData.D.value('.', 'VARCHAR(2)') RecruiterID
                              FROM s.xmlcol.nodes('X') ProjectData(D)
               ) a
)
,CTE1 AS 
(              
               SELECT JobId,JobName,RecruiterName FROM CTE c INNER JOIN Recruiter r on r.RecruiterID = c.RecruiterID
)
SELECT JobId, JobName , STUFF 
                ((
                SELECT ', ' + a.RecruiterName 
                FROM CTE1 a
                WHERE ( a.JobId = b.JobId )
                FOR XML PATH('')
                ) ,1,2,'') 
                AS RecruiterNames
FROM CTE1 b
GROUP BY JobId, JobName


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com