Search code examples
sqlsql-servert-sqlpivot-tabletranspose

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

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
Solution

  • 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