SQL Server Pivot Date

Marchel Gunawan Dwi Steya 0
Viewed 37

I'm having a difficult time with Pivot in SQL Server.

My table has the following data:

ID Date STEP
1 1/1/2022 1
1 2/2/2022 2
2 1/1/2022 1
2 3/3/2022 2

What I need is the following result:

ID Step_1 Step_2
1 1/1/2022 2/2/2022
2 1/1/2022 3/3/2022

Is there any way to use PIVOT in this problem? Or any other way to solve the problem?

pivot tsql sql-server sql
2 Answers

Assuming there would only ever be 2 steps and the dates increase with step number, then you may use a simple GROUP BY query here:

SELECT ID, MIN(Date) AS Step_1, MAX(Date) AS Step_2
FROM yourTable
GROUP BY ID
ORDER BY ID;

Otherwise, for a more robust query, use formal pivoting logic:

SELECT ID,
       MAX(CASE WHEN STEP = 1 THEN Date END) AS Step_1,
       MAX(CASE WHEN STEP = 2 THEN Date END) AS Step_2
FROM yourTable
GROUP BY ID
ORDER BY ID;
Tim Biegeleisen0

First of all, use Tim Biegeleisen's answer unless you have a very good reason. The PIVOT clause takes almost as much code as the MAX(CASE....) version but is harder to write and read.

And I can never remember the syntax. I always have to look it up.

Using PIVOT, you need to

SELECT ID,
       [1] as Step_1, 
       [2] as Step_2
FROM  
(
  SELECT ID,Date,Step
  FROM yourTable
) AS SourceTable  
PIVOT  
(  
  MAX(Date)  
  FOR Step IN ([1], [2])  
) AS PivotTable;

In SQL you can't have an arbitrary number of columns, or multiple values per column. The PIVOT clause identifies the Step values that become columns and the aggregate function used to reduce potentially multiple "cell" values into a single one.

Compare this with this equivalent:

SELECT ID,
       MAX(CASE WHEN STEP = 1 THEN Date END) AS Step_1,
       MAX(CASE WHEN STEP = 2 THEN Date END) AS Step_2
FROM yourTable
GROUP BY ID
ORDER BY ID;

Which one is clearer?

What if you decide to use different aggregate functions per step?

SELECT ID,
       MIN(CASE WHEN STEP = 1 THEN Date END) AS Step_1,
       MAX(CASE WHEN STEP = 2 THEN Date END) AS Step_2
FROM yourTable
GROUP BY ID
ORDER BY ID;
Panagiotis Kanavos0
Related