Join Query:Please Help

  • 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:)

  • 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