SQL Query help required

  • Hello Friends,

    I have three tables are there as mentioned below, tblCommon, tblSkill and tblCommon_tblSkills. TblSkills is master table and tblCommon_tblSkills table. Here my requirement is a report, it should be like all tblCommon columns should require and all rows of the tblSkills should become the columns, and value for these columns should be Yes or No depending on the tblCommon_tblSkills table. Please find the attached spread sheet for more details.

    Please can anyone build the SQL query get the output. Please do the needful at the earliest.

    1) TableName: tblCommon

    IDFnameLnameAgeSexLocation

    1KishoreP37MHYD

    2KotiR28MHYD

    3RameshB30MHYD

    2) Table Name: tblSkills

    IDSkillName

    1MSOffice

    2SharePoint

    3ASP.Net

    4C#

    5J2EE

    6SQLServer

    7Oracle

    3) Table Name: tblCommon_tblSkills

    Common_IDSkill_ID

    11

    12

    13

    14

    16

    21

    22

    23

    24

    25

    26

    27

    31

    33

    34

    36

    The required out put from these tables should be in the following format.

    Output

    IDFnameLnameAgeSexLocationMSOfficeSharePointASP.NetC#J2EESQLServerOracle

    1KishoreP37MHYDYesYesYesYesNoYesNo

    2KotiR28MHYDYesYesYesYesYesYesYes

    3RameshB30MHYDYesNoYesYesNoYesNo

  • Hi,

    You find below a solution:

    SELECT

    com.IDAS ID,

    com.FnameAS Fname,

    com.LnameAS Lname,

    com.AgeAS Age,

    com.SexAS Sex,

    com.LocationAS Location,

    CASE WHEN EXISTS (SELECT '1' FROM tblCommon_tblSkills comski

    left JOIN tblskills ski ON comski.skill_id=ski.id WHERE comski.common_id=com.id AND ski.id=1) THEN 'Yes' ELSE 'No' END AS MSOffice,

    CASE WHEN EXISTS (SELECT '1' FROM tblCommon_tblSkills comski

    left JOIN tblskills ski ON comski.skill_id=ski.id WHERE comski.common_id=com.id AND ski.id=2) THEN 'Yes' ELSE 'No' END AS SharePoint,

    CASE WHEN EXISTS (SELECT '1' FROM tblCommon_tblSkills comski

    left JOIN tblskills ski ON comski.skill_id=ski.id WHERE comski.common_id=com.id AND ski.id=3) THEN 'Yes' ELSE 'No' END AS 'ASP.Net',

    CASE WHEN EXISTS (SELECT '1' FROM tblCommon_tblSkills comski

    left JOIN tblskills ski ON comski.skill_id=ski.id WHERE comski.common_id=com.id AND ski.id=4) THEN 'Yes' ELSE 'No' END AS 'C#',

    CASE WHEN EXISTS (SELECT '1' FROM tblCommon_tblSkills comski

    left JOIN tblskills ski ON comski.skill_id=ski.id WHERE comski.common_id=com.id AND ski.id=5) THEN 'Yes' ELSE 'No' END AS 'J2EE',

    CASE WHEN EXISTS (SELECT '1' FROM tblCommon_tblSkills comski

    left JOIN tblskills ski ON comski.skill_id=ski.id WHERE comski.common_id=com.id AND ski.id=6) THEN 'Yes' ELSE 'No' END AS SQLServer,

    CASE WHEN EXISTS (SELECT '1' FROM tblCommon_tblSkills comski

    left JOIN tblskills ski ON comski.skill_id=ski.id WHERE comski.common_id=com.id AND ski.id=7) THEN 'Yes' ELSE 'No' END AS Oracle

    FROM

    tblcommon com

    The results are the following:

    IDFnameLnameAgeSexLocationMSOfficeSharePointASP.NetC#J2EESQLServerOracle

    1KishoreP37mhydYesYesYesYesNoYesNo

    2KotiR28mhydYesYesYesYesYesYesYes

    3RameshB30mhydYesNoYesYesNoYesNo

    Wish you good ideas! 🙂
    Andreea

  • It will probably be more efficient to PIVOT tblCommon_tblSkills.

    Something like:

    ;WITH Skills

    AS

    (

    SELECT Common_ID, [1],[2],[3],[4],[5],[6],[7]

    FROM

    (

    SELECT Common_ID, Skill_ID

    FROM tblCommon_tblSkills

    ) P

    PIVOT

    (

    COUNT(Skill_ID)

    FOR Skill_ID IN ([1],[2],[3],[4],[5],[6],[7])

    ) A

    )

    SELECT C.*

    ,CASE WHEN S.[1] = 1 THEN 'Yes' ELSE 'No' END AS MSOffice

    ,CASE WHEN S.[2] = 1 THEN 'Yes' ELSE 'No' END AS ScarePoint

    ,CASE WHEN S.[3] = 1 THEN 'Yes' ELSE 'No' END AS [ASP.Net]

    ,CASE WHEN S.[4] = 1 THEN 'Yes' ELSE 'No' END AS [C#]

    ,CASE WHEN S.[5] = 1 THEN 'Yes' ELSE 'No' END AS J2EE

    ,CASE WHEN S.[6] = 1 THEN 'Yes' ELSE 'No' END AS SQLServer

    ,CASE WHEN S.[7] = 1 THEN 'Yes' ELSE 'No' END AS Oracle

    FROM tblCommon C

    JOIN Skills S

    ON C.[ID] = S.Common_ID

  • Yes, you're right, and it works if the database compatibility level is 90.

    Even if I work with SQL Server 2005, because of the application, my dbs compatiliblty levels are set to 80

    So, that's another better solution. 🙂

    Wish you good ideas! 🙂
    Andreea

  • Thanks Andreea and Ken McKelvey for your quick response. The queries are awesome.

    But if a new row added to tblSkills table, then how to get those columns dynamically?

    Can you please help me in this regard.

    Thanks and Regards

    Kishore

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply