Transpose rows after Join and Groupby in SQL Server table

Question

I have some SQL Server tables:

• Employees [EmpID (pk), Name, Designation]
• Projects [PID (pk), EmpID (fk), PCode, PName, Dept]

The tables have a many-to-one relation, that is, one employee can work on multiple projects.

I want to show the result in such a way that each employee has only one row and all the projects he worked on are shown in the column section grouped according to the PCode.

I am unable to understand how to transpose the rows into the column when the number of columns generated are unknown.

Sample Data:

Employee:

EmpId  Name  Designation
------------------------
001    Mat   Manager
002    Ash   Developer
003    Paul  Analyst

Projects:

PID EmpID PCode PName       Dept
-------------------------------------
1   001   111   Project1    Sales
2   001   111   Project1.1  Retail
3   001   222   Project2    Banking
4   002   222   Project2.1  Retail   

Sample output:

EmpID Name Project-1           Project-2              Project-3           
---------------------------------------------------------------------------
001   Abc  111,Project1,Sales  111,Project1.1,Retail  222,Project2,Banking
002   Def  222,Project2.1,Retail

Answer 1

You can enumerate the projects of each employee in a subquery with row_number(), then use conditional aggregation to pivot in the outer query:

select e.empid, e.name,
    max(case when rn = 1 then concat_ws(',', p.pcode, p.pname, p.pdept) end) project1,
    max(case when rn = 2 then concat_ws(',', p.pcode, p.pname, p.pdept) end) project2,
    max(case when rn = 3 then concat_ws(',', p.pcode, p.pname, p.pdept) end) project3
from employee e
inner join (
    select p.*, row_number() over(partition by empid order by pid) rn
    from projects p
) p on p.empid = e.empid
group by e.empid, e.name