Sql-server – SQL query to find Employees Skiils,Companies and projects

sql servert-sql

This is my ER diagram : enter image description here

Actually I tried to write it with recursive CTE but I did not succeed
I was wondering If you could help me with that
Thank you

Sample Data

EmployeeInfo: 

EmployeeID   FirstName
---------   ---------
  1            A
  2            B

Skill:

SkillID   SkillName
--------  ---------
  1         Java
  2         Python
  3          CSS

Company:

CompanyID    CompanyName
---------    -----------
   1             X
   2             Y

Project :

Projectcode   ProjectDesc
----------    ------------
1                GTHY
2                GYHU

I need to write a query to give me this result (The Expected Result)

EmployeeID  EmployeeName   Skills       Companies     Projects 
---------   ------------   ------       ---------     ---------

   1           A        Java,Python,Css    X,Y            GTHY

I mean The EmployeeID and Name , His skills , Companies he had worked on and projects he had participated in

Best Answer

You can solve this types of problems with a simple "For XML Path" clause in sql. You can do this by:

SELECT
E.EmployeeID,
E.FirstName+' '+E.LastName AS EmployeeName,
(
    SELECT SkillName+', ' FROM Skill WHERE SkillID IN
    (
        SELECT SkillID FROM EmployeeSkill WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Skills,
(
    SELECT Name+', ' FROM Company WHERE CompanyID IN (
        SELECT CompanyID FROM EmployeeCompany WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Companies,
(
    SELECT ProjectName+', ' FROM Project WHERE ProjectID IN
    (
        SELECT Project FROM EmployeeProject WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Projects
FROM EmployeeInfo AS E

UPDATE

You can try this to remove comma(,) at last of skills, companies & project..

SELECT EmployeeID,
EmployeeName,
left(Skills,LEN(Skills)-1) AS Skills,
left(Companies,LEN(Companies)-1) AS Companies,
left(Projects,LEN(Projects)-1) AS Projects
FROM(
    SELECT E.EmployeeID AS EmployeeID, E.FirstName+' '+E.LastName AS EmployeeName,
    (
        SELECT SkillName+', ' FROM Skill WHERE SkillID IN
        (
            SELECT SkillID FROM EmployeeSkill WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Skills,
    (
        SELECT Name+', ' FROM Company WHERE CompanyID IN
        (
            SELECT CompanyID FROM EmployeeCompany WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Companies,
    (
        SELECT ProjectName+', ' FROM Project WHERE ProjectID IN
        (
            SELECT Project FROM EmployeeProject WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Projects
    FROM EmployeeInfo AS E
) AS Tmp
Related Question