sql-server - 具有多个非枢轴列的 T-SQL 数据透视表

Question

我有几列并制作枢轴。我想要多个非枢轴列并使用最后一个进行枢轴。在原始规范here它表明您只能有一个非枢轴列。

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

有没有办法拥有更多的非枢轴列，因为它使用第一个列之后的所有列来旋转我的数据。

Answer 1

是的。只需添加它们。

例如

declare @t table (a int, b int, c int, d int)
insert @t values (1,2,3,4)
insert @t values (7,6,5,3)

select a,b, [3],[4] from @t s
pivot 
(sum(c) for d in ([3],[4])) p