How do I pivot in SQL

Question

I have a table which I am trying to Pivot. I am partially able to achieve what I want. I need help in completing it

My base table

+-------------+----------+-------------+
| WorkOrderID | STEP_NBR | STEP_STATUS |
+-------------+----------+-------------+
| AA100       |        0 | C           |
| AA100       |        1 | W           |
| AA100       |        2 | W           |
| AA200       |        0 | W           |
| AA200       |        1 | W           |
| AA300       |        0 | C           |
| AA300       |        1 | C           |
+-------------+----------+-------------+

My Desired Output

+-------------+--------+--------+--------+
| WorkOrderID | Step_0 | Step_1 | Step_2 |
+-------------+--------+--------+--------+
| AA100       | C      | W      | W      |
| AA200       | W      | W      |        |
| AA300       | C      | C      |        |
+-------------+--------+--------+--------+

Output I am getting

+-------------+--------+--------+--------+
| WorkOrderID | Step_0 | Step_1 | Step_2 |
+-------------+--------+--------+--------+
| AA100       | C      | null   | null   |
| AA100       | null   | W      | null   |
| AA100       | null   | null   | W      |
| AA200       | W      | null   | null   |
| AA200       | null   | W      | null   |
| AA300       | C      | null   | null   |
| AA300       | null   | C      | null   |
+-------------+--------+--------+--------+

My Query

SELECT WorkOrderID, 
(CASE WHEN STEP_NBR = 0 THEN STEP_STATUS ELSE NULL END) AS Step_0,
(CASE WHEN STEP_NBR = 1 THEN STEP_STATUS ELSE NULL END) AS Step_1,
(CASE WHEN STEP_NBR = 2 THEN STEP_STATUS ELSE NULL END) AS Step_2
FROM Table1

How do I get rid of multiple WorkOrderId. I just want 1 row for each WorkOrderId

db-fiddle

score 3 · Accepted Answer · answered Feb 09 '19 at 19:24

3

You just need aggregation:

SELECT WorkOrderID, 
       MAX(CASE WHEN STEP_NBR = 0 THEN STEP_STATUS END) AS Step_0,
       MAX(CASE WHEN STEP_NBR = 1 THEN STEP_STATUS END) AS Step_1,
       MAX(CASE WHEN STEP_NBR = 2 THEN STEP_STATUS END) AS Step_2
FROM Table1
GROUP BY WorkOrderID;

ELSE NULL is redundant, so I removed it.

answered Feb 09 '19 at 19:24

Gordon Linoff

1,242,037
58
646
786

Thanks! Can you please explain how does `MAX` in this case works – sql_learner Feb 09 '19 at 19:26
1

The `GROUP BY` produces one row per `WorkOrderId`. The `MAX()` is choosing one value that matches each condition. – Gordon Linoff Feb 09 '19 at 19:48

How do I pivot in SQL

1 Answers1