July 8, 2012 at 9:27 pm
Requirement Table
SO_ID REQ_ID SKILLCODE0001 00011 0001693-5-4, 0001798-2-3
0002 00011 0001234-3-2,0003456-8-9,0004534-7-6
0003 00012 0001456-0-1
Skills Table
SKill Code Skill Desc0001693 Java
0001798 .Net
0001234 Mainframe
0003456 Linux
0004534 Ajax
0001456 Perl
Result after Join:
SO_ID REQ_ID SKILLCODE Skill_Desc0001 00011 0001693-5-4, 0001798-2-3 Java,.Net
0002 00011 0001234-3-2,0003456-8-9,0004534-7-6 Mainframe,linux,Ajax
0003 00012 0001456-0-1 Perl
Please Help...thanks in advance:)
July 8, 2012 at 11:44 pm
First, lets set-up the sample data. It would much much easier for the people who respond, to have the data already set-up for testing. Hope your future questions follow the etiquettes.
DECLARE @Requirements TABLE
(
SO_ID VARCHAR(4)
,Req_id VARCHAR(10)
,Skill_Code VARCHAR(100)
);
INSERT INTO @Requirements (SO_ID ,Req_id , Skill_Code)
SELECT '0001' ,'00011', '0001693-5-4, 0001798-2-3'
UNION ALL SELECT '0002' ,'00011', '0001234-3-2,0003456-8-9,0004534-7-6'
UNION ALL SELECT '0003' ,'00012', '0001456-0-1'
;
DECLARE @skills TABLE
(
SKill_Code VARCHAR(100)
,Skill_Desc VARCHAR(100)
);
INSERT INTO @skills (SKill_Code , Skill_Desc)
SELECT '0001693' ,'Java'
UNION ALL SELECT '0001798' ,'.Net'
UNION ALL SELECT '0001234' ,'Mainframe'
UNION ALL SELECT '0003456' ,'Linux'
UNION ALL SELECT '0004534' ,'Ajax'
UNION ALL SELECT '0001456' ,'Perl'
;
Now the code:
;WITH CTE AS
(
SELECT Req.SO_ID , Req.Req_id , CrsApp.Skills , Skl.Skill_Desc
FROM @Requirements Req
CROSS APPLY ( SELECT RTRIM( LTRIM (Item))
FROM dbo.DelimitedSplit8K(Req.Skill_Code , ',')
) CrsApp (Skills)
LEFT JOIN @skills Skl
ON Skl.SKill_Code = SUBSTRING( Skills , 1, CHARINDEX('-',Skills)-1)
)
SELECT C.SO_ID , C.Req_id
, SkillCodes =
STUFF
(
(
SELECT ',' + InrTbl.Skills
FROM CTE InrTbl
WHERE InrTbl.SO_ID = c.SO_ID
AND InrTbl.Req_id = C.Req_id
FOR XML PATH(''), TYPE
).value('./text()[1]', 'VARCHAR(MAX)')
, 1, 1, SPACE(0)
)
, SkillDesc =
STUFF
(
(
SELECT ',' + InrTbl.Skill_Desc
FROM CTE InrTbl
WHERE InrTbl.SO_ID = c.SO_ID
AND InrTbl.Req_id = C.Req_id
FOR XML PATH(''), TYPE
).value('./text()[1]', 'VARCHAR(MAX)')
, 1, 1, SPACE(0)
)
FROM CTE C
GROUP BY C.SO_ID , C.Req_id
;
For information on the dbo.DelimitedSplit8k function , refer this link : dbo.DelimitedSplit8k function[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply