Split multiple rows into multiple columns

September 30, 2022

I have a table with 2 Columns:

SELECT
ID ,Project
FROM projects

The table shows me the projects in which the Employee(ID) is working in:

ID | Project
--------------
ID1| Project1
ID1| Project2
ID1| Project3
ID1| Project4
ID2| Project1
ID2| Project2
ID3| Project1
ID3| Project2
ID3| Project3

In case when one employee is working in more than 1 project, i need to split the different projects into different columns named . The result should be looking like this:

ID |   Col1   |   Col2   |   Col3   |   Col4   |   Col5  |
----------------------------------------------------------
ID1| Project1 | Project2 | Project3 | Project4 |   NULL  |
ID2| Project1 | Project2 |   NULL   |   NULL   |   NULL  |
ID3| Project1 | Project2 | Project3 |   NULL   |   NULL  |

Since there are never more than 5 projects, the number of the columns can be fixed at 5.

I tried with different row_number() and pivot approaches but failed due to my inexperience.

>Solution :

We can use a pivot query with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Project) rn
    FROM projects
)

SELECT
    ID,
    MAX(CASE WHEN rn = 1 THEN Project END) AS Col1,
    MAX(CASE WHEN rn = 2 THEN Project END) AS Col2,
    MAX(CASE WHEN rn = 3 THEN Project END) AS Col3,
    MAX(CASE WHEN rn = 4 THEN Project END) AS Col4,
    MAX(CASE WHEN rn = 5 THEN Project END) AS Col5
FROM cte
GROUP BY ID
ORDER BY ID;

If there be some other column which should determine the ordering of projects for each ID, then update the call to ROW_NUMBER.