How to transpose rows to columns in SQL SERVER without creating staging table

Question

Please help me to transpose the below table, without creating a staging table. Tried PIVOT but failed. Any suggestions will also be helpful. Thanks!

Existing table:

TIME	Value
4/8/2020 5:18	1
4/8/2020 5:22	0
4/8/2020 7:22	1
4/8/2020 7:31	0
4/9/2020 1:44	1
4/9/2020 1:50	0

Desired results:

1	0
4/8/2020 5:18	4/8/2020 5:22
4/8/2020 7:22	4/8/2020 7:31
4/9/2020 1:44	4/9/2020 1:50

How did PIVOT "fail"? I'd suggest you do some more research and try [PIVOT](https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/) again. This is definitely what you're looking to do. — WAMLeslie, Jun 03 '21 at 12:08
Yes, I already included one link in my original comment as a starting point. — WAMLeslie, Jun 03 '21 at 12:12
PIVOT is failing because the aggregate function MAX() or MIN() on the time columns is returning only 1 row from the expected result. Any suggestions? — Mamunur Rashid, Jun 03 '21 at 12:20

score 1 · Answer 1 · answered Jun 03 '21 at 12:08

1

You can enumerate them and aggregate:

select max(case when value = 1 then time end) as value_1,
       max(case when value = 0 then time end) as value_0
from (select t.*,
             row_number() over (partition by value order by time) as seqnum
      from t
     ) t
group by seqnum;

answered Jun 03 '21 at 12:08

Gordon Linoff

1,242,037
58
646
786

Thanks! It works too. But can't mark both answers as solved. – Mamunur Rashid Jun 03 '21 at 12:42
@MamunurRashid . . . This was 16 minutes before the other answer. – Gordon Linoff Jun 03 '21 at 12:45

score 0 · Accepted Answer · answered Jun 03 '21 at 12:25

Try this solution in combination ROW_NUMBER window function and PIVOT:

SELECT [0], [1]
FROM 
(
  SELECT Value, TIME, ROW_NUMBER() OVER (PARTITION BY Value ORDER BY TIME) Rb
  FROM MyTable
) AS SourceTable
PIVOT
(
  MAX(TIME)
  FOR Value IN ([0], [1])
) AS PivotTable

How to transpose rows to columns in SQL SERVER without creating staging table

2 Answers2