pivot - Create a SQL query that merges rows

Question

I have a table that stores the dates when an order was opened and closed. It's similar to this:

id	orderID	status	date
1	1	opened	2020-01-01
2	1	closed	2020-01-05
3	2	opened	2020-01-02

Answer 1

You should get a syntax error, because the select columns are inconsistent with the group by. Use aggregation:

SELECT orderId,
       MAX(CASE WHEN status = 'opened' THEN date END) AS openedDate,
       MAX(CASE WHEN status = 'closed' THEN date END) AS closedDate
FROM orders
GROUP BY orderId;