Long table to wide on two columns

Question

I would like to widen a long table that has two columns to widen. I've found solutions for converting a long table to wide, but they all take one column and convert it to n columns. I would like to take two columns and convert the table to 2n columns.

I used this solution to successfully widen a table based on one column, but I'm not sure how to widen a second column.

Here is a sample data set similar to that of the linked solution:

Date        Person    Number1    Number2
2015-01-03  A         4          6
2015-01-04  A         2          5
2015-01-05  A         3          1
2015-01-03  B         5          3
2015-01-04  B         6          4
2015-01-05  B         7          6

I want to widen both columns "Number1" and "Number2" such that the output is:

Date          A1   B1   A2   B2
2015-01-03    4    5    6    3
2015-01-04    2    6    5    4
2015-01-05    3    7    1    6

The solution then, based on the answer by dcieslak for widening the one column, would be:

select Date, 
       isNull([A], 0) as A1,
       isNull([B], 0) as B1
from
    ( select Date, Person, Number1, Number2
      from tbl ) AS SourceTable
PIVOT
( max(Number1) for Person in ( [A], [B]) ) AS PivotTable;

But how about for the second? I tried adding a second line in PIVOT but that didn't work. I guess I could split the table into two parts and join the resulting wide tables but that seems like a bad idea because it would create such long code.

score 1 · Accepted Answer · answered May 21 '22 at 08:33

1

for multi columns pivot, it is easier to use case expression

select [Date],
       A1 = max(case when Person = 'A' then Number1 end),
       B1 = max(case when Person = 'B' then Number1 end),
       A2 = max(case when Person = 'A' then Number2 end),
       B2 = max(case when Person = 'B' then Number2 end)
from   SourceTable
group by [Date]

answered May 21 '22 at 08:33

Squirrel

23,507
4
34
32

Preferred this solution a bit more. – hmhensen May 23 '22 at 17:07

score 1 · Answer 2 · answered May 21 '22 at 08:44

With 2n you do not need pivot, a simple JOIN will do:

SELECT
    a.Date,
    a.Number1 as "A1",
    b.Number1 as "B1",
    a.Number2 as "A2",
    b.Number2 as "B2"
FROM tbl a
INNER JOIN tbl b on b.Date=a.Date AND b.Person='B'
WHERE a.Person='A'

see: DBFIDDLE

Long table to wide on two columns

2 Answers2