包含3行的SQL Pivot表 [英] SQL Pivot table with 3 rows as a group

问题描述

我正在使用SQL2012.我有下面的SQL表，其中显示了案例编号及其值.案例编号始终以组的形式出现3个案例.你能帮我摆桌子吗?案件数超过一千.

I am using SQL2012. I have the following SQL table which shows case number and their value. The case number always appears 3 cases as a group. Can you help me to pivot the table? The number of case number is more than a thousand.

case  value
A     1992
A     1956
A     2007
B     2000
B     2001
B     1990
C     2000
C     1956
C     1817

预期结果:

Case  value1   value2   value3
A    1992      1956     2007
B    2000      2001     1990
C    2000      1956     1817

推荐答案

使用ROW_NUMBER并应用条件聚合:

WITH Cte AS(
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY [case] ORDER BY (SELECT NULL))
    FROM Tbl
)
SELECT
    [case],
    value1 = MAX(CASE WHEN Rn = 1 THEN value END),
    value2 = MAX(CASE WHEN Rn = 2 THEN value END),
    value3 = MAX(CASE WHEN Rn = 3 THEN value END)
FROM Cte
GROUP BY [case];

在线演示

这篇关于包含3行的SQL Pivot表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！