MS SQL Server. Transpose columns of data to rows of data

Question

I have a Details table which can be described by the following:

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

We insert some seed data:

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)

Select All produces the following:

I would like to transpose the column data to rows, to produce the output below:

Thanks in advance.

Which database? Does `Channelld` contain only 0,1,2,3,4 values or you can have more? If not you can use PIVOT in SQL Server. — GGadde, Nov 05 '18 at 14:28
SQL Server. I thought you could use pivot, but I am not getting the query right. Hence the posing of the question here. Thanks. — AndyS, Nov 05 '18 at 14:31
If you know that the values of `0,1,2,3,4` are going to be used in the pivot ahead of time, then a plain old `pivot` is the way to go. If you don't know what the values of the column headers will be before running the sql, then you'll want to do a `Dynamic Pivot`. You'll have to let us know which scenario fits here to get a proper answer as the solution will be a bit different. — JNevill, Nov 05 '18 at 14:34

score 4 · Accepted Answer · answered Nov 05 '18 at 14:38

Here you go. Just select first HeaderId,value, ChannelId in a subquery and then pivot.

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;

Result below:

Thank you! I'd missed HeaderId from the query! – AndyS Nov 05 '18 at 15:23 — AndyS, Nov 05 '18 at 15:23

score 1 · Answer 2 · answered Nov 05 '18 at 14:39

1

This should work:

select HeaderId
  , [0] as ChannelId0
  , [1] as ChannelId1
  , [2] as ChannelId2
    , [3] as ChannelId3
      , [4] as ChannelId4
from
(
  select HeaderId, ChannelId,Value
  from #Details
) x
pivot
(
  max(Value)
  for ChannelId in([0],[1],[2], [3],[4],[5])
)p

answered Nov 05 '18 at 14:39

GGadde

391
1
14

Thank you! I'd missed HeaderId from the query! – AndyS Nov 05 '18 at 15:25

MS SQL Server. Transpose columns of data to rows of data

2 Answers2