CODEWITHSUNDEEP
sqlsql-server
Jonathan Pilon-Carriere
Jonathan Pilon-Carriere

Reputation: 3

SQL Server question : manipulate column to a row

I got this table with a lot of rows:

ID date 1 date 2
1 2001-09-09 1992-10-10
2 2008-07-07 2021-01-01

And I would like to write a query to get this kind of result:

Id Date
1 2001-09-09
1 1992-10-10
2 2008-07-07
2 2021-01-01

Thanks for the help

Upvotes: 0

Views: 49

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81960

One option is using a CROSS APPLY

Select A.ID
      ,B.*
 From  YourTable A
 Cross Apply ( values ([date 1])
                     ,([date 2])
             )B(Date)

Upvotes: 2

anon
anon

Reputation:

The absolute simplest way:

SELECT ID, [Date] = [date 1] FROM dbo.TableName
UNION ALL
SELECT ID, [Date] = [date 2] FROM dbo.TableName
ORDER BY ID;

On larger tables, though, other solutions that don't scan twice (like CROSS APPLY) will fare better. Or UNPIVOT:

SELECT ID, [Date]
  FROM dbo.TableName
  UNPIVOT ([Date] FOR d IN ([Date 1],[Date 2])) AS p
  ORDER BY ID;

Upvotes: 1

Related Questions