Creating a pivot like report in SQL Server

Question

I have a table with values like the table below:

ID	Name
1	N1
2	N2
3	N1
4	N1
5	N3
6	N2

And I want to create a report that looks like this:

Name	ID1	ID2	ID3
N1	1	3	4
N2	2	6	-
N3	5	-	-

There are two obstacles with this problem, first, the number of IDs for the same name is not fixed and, second column names for IDs should be generated.

I have managed to write a query that generates this report and then by using Dynamic Query, I think I can create the report.

select * from(
select T1.Name,T1.ID as ID1,T2.ID as ID2,T3.ID as ID3, ROW_NUMBER() over (PARTITION BY T1.Name ORDER BY T1.ID) as [Order]
from TestTabel T1 left join TestTabel T2
    on T1.Name=T2.Name and T1.ID<T2.ID left join TestTabel T3
    on T2.Name=T3.Name and T2.ID<T3.ID)T
where T.[Order]=1

But, it seems there should be a better and more straightway solution for it.

Thank you in advance.

Search for dynamic pivot sql server here on stackoverflow. There's at least one question per day about it — siggemannen, Apr 16 '23 at 08:56
Thank you. yes, I Think I have some difficulties understanding how dynamic pivot works. — shabnam aboulghasem, Apr 17 '23 at 09:50

score 0 · Accepted Answer · answered Apr 16 '23 at 07:45

Assuming you know the maximum number of IDs per name, you may use a pivot query with the help of ROW_NUMBER:

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

SELECT
    Name,
    MAX(CASE WHEN rn = 1 THEN ID END) AS ID1,
    MAX(CASE WHEN rn = 2 THEN ID END) AS ID2,
    MAX(CASE WHEN rn = 3 THEN ID END) AS ID3,
    MAX(CASE WHEN rn = 4 THEN ID END) AS ID4,
    MAX(CASE WHEN rn = 5 THEN ID END) AS ID5
FROM cte
GROUP BY Name
ORDER BY Name;

Thank you for your answer, I can obtain the max number by aggregation and then use your query. — shabnam aboulghasem, Apr 16 '23 at 10:40

Creating a pivot like report in SQL Server

1 Answers1