sql - 微软 SQL 服务器。将数据列转置为数据行

Question

我有一个详细信息表，可以通过以下方式进行描述:

Create table #Details (
Id int identity,
HeaderId int,
ChannelId int,
Value float
)

我们插入一些种子数据:

insert into #Details (HeaderId, ChannelId, Value) values(1, 0, 1019.51)
insert into #Details (HeaderId, ChannelId, Value) values(1, 1, 20.1)
insert into #Details (HeaderId, ChannelId, Value) values(1, 2, 21.2)
insert into #Details (HeaderId, ChannelId, Value) values(1, 3, 22.3)
insert into #Details (HeaderId, ChannelId, Value) values(1, 4, 23.4)

insert into #Details (HeaderId, ChannelId, Value) values(2, 0, 1020.62)
insert into #Details (HeaderId, ChannelId, Value) values(2, 1, 26.1)
insert into #Details (HeaderId, ChannelId, Value) values(2, 2, 27.2)
insert into #Details (HeaderId, ChannelId, Value) values(2, 3, 28.3)
insert into #Details (HeaderId, ChannelId, Value) values(2, 4, 29.4)

全选会产生以下结果:

我想将列数据转置为行，以产生以下输出:

提前致谢。

Answer 1

给你。只需在子查询中选择第一个 HeaderId,value, ChannelId，然后进行透视即可。

DECLARE @tbl TABLE (Id int identity,
HeaderId int,
ChannelId int,
Value float)

insert into @tbl (HeaderId, ChannelId, Value) values(1, 0, 1019.51)
insert into @tbl (HeaderId, ChannelId, Value) values(1, 1, 20.1)
insert into @tbl (HeaderId, ChannelId, Value) values(1, 2, 21.2)
insert into @tbl (HeaderId, ChannelId, Value) values(1, 3, 22.3)
insert into @tbl (HeaderId, ChannelId, Value) values(1, 4, 23.4)

insert into @tbl (HeaderId, ChannelId, Value) values(2, 0, 1020.62)
insert into @tbl (HeaderId, ChannelId, Value) values(2, 1, 26.1)
insert into @tbl (HeaderId, ChannelId, Value) values(2, 2, 27.2)
insert into @tbl (HeaderId, ChannelId, Value) values(2, 3, 28.3)
insert into @tbl (HeaderId, ChannelId, Value) values(2, 4, 29.4)

SELECT * 
FROM
    (
    SELECT HeaderId,value, ChannelId
        FROM
    @tbl
    )T
PIVOT
    (
        SUM(value)

        FOR ChannelId IN ([0],[1], [2], [3], [4]

    )
) AS PivotTable;

结果如下: