I got this table with a lot of rows:

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

Thanks for the help

CodePudding user response：

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;

• Example db<>fiddle

CodePudding user response：

One option is using a CROSS APPLY

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