Creating a pivot like report in SQL Server [duplicate]

p4rjhz4m 于 2023-04-19 发布在 SQL Server

关注(0)|答案(1)|浏览(116)

This question already has answers here:

Convert Rows to columns using 'Pivot' in SQL Server (9 answers)
Closed 2 days ago.

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.

sql-server

来源：https://stackoverflow.com/questions/76026468/creating-a-pivot-like-report-in-sql-server

1条答案

按热度按时间

cbeh67ev1#

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;

赞(0）回复(0）举报 2023-04-19

我来回答

Creating a pivot like report in SQL Server [duplicate]

1条答案

相关问题

热门标签

最新问答