SQL透视表-复制Excel功能

这真是一个 UNPIVOT 以及 PIVOT . 以下代码在单个查询中实现了所需的结果。

DECLARE @t TABLE (
    Task     varchar(5),
    Type     char(1),
    Variable char(1),
    Hours    int,
    Duration int
    ) 

INSERT INTO @t
    VALUES
        ('One',   'A', 'X',  10,  5),
        ('One',   'A', 'Y',  40, 15),
        ('One',   'B', 'X', 100, 29),
        ('Two',   'A', 'X',   5,  2),
        ('Two',   'B', 'X',  15,  9),
        ('Two',   'A', 'Y',  60, 17),
        ('Three', 'A', 'Y',  18,  5)

SELECT
        P.Task,
        P.Type,
        CAST(P.Property AS varchar(8)) AS Property,
        COALESCE(P.X, 0) AS X,
        COALESCE(P.Y, 0) AS Y
    FROM @t AS T
    UNPIVOT (
        Value FOR Property IN (
            Hours,
            Duration
            )
        ) AS U
    PIVOT (
        SUM(Value) FOR Variable IN (
            X,
            Y
            )
        ) AS P

Task  Type Property X           Y
----- ---- -------- ----------- -----------
One   A    Duration 5           15
One   A    Hours    10          40
One   B    Duration 29          0
One   B    Hours    100         0
Three A    Duration 0           5
Three A    Hours    0           18
Two   A    Duration 2           17
Two   A    Hours    5           60
Two   B    Duration 9           0
Two   B    Hours    15          0

如您所见,小时和持续时间是翻转的。我不认为有任何办法强迫命令使用 枢轴 Property 具有关联排序顺序的值,只要您有其他方法确保其他列首先正确排序。