I'm trying to convert rows into columns but without success.
I've tried to use a pivot function, but it seems not to be possible because of my values is in time format, not numeric.
I have a table looking like this (including about 25 more columns except from these 3 and hundrede thousands of rows):
| Order_ID | Order_Event | Timestamp |
|---|---|---|
| 12345 | Order_Created | 2023-07-08T07:59 |
| 12345 | Order_delivered | 2023-07-09T10:09 |
| 12345 | Order_planned_delivery | 2023-07-08T10:00 |
| 67890 | Order_Created | 2023-07-10T11:40 |
| 67890 | Order_delivered | 2023-07-09T10:09 |
| 67890 | Order_planned_delivery | 2023-07-08T20:30 |
| 10111 | Order_Created | 2023-06-03T20:51 |
| 10111 | Order_delivered | 2023-07-12T18:26 |
| 10111 | Order_planned_delivery | 2023-07-12T18:30 |
And what I want is something like this with order_event as column names:
| Order_ID | Order_created | Order_delivered | Order_planned_delivery |
|---|---|---|---|
| 12345 | 2023-07-08T07:59 | 2023-07-09T10:09 | 2023-07-08T10:00 |
| 67980 | 2023-07-10T11:40 | 2023-07-09T10:09 | 2023-07-08T20:30 |
| 10111 | 2023-06-03T20:51 | 2023-07-12T18:26 | 2023-07-12T18:30 |
I tried this code as you can see below, and the result gives me the maximum value of the time (from the timestamp, will do the same with dates too) that the order is created, delivered and planned delivery, and it seems to bee right so long, but it just one row in the result:
SELECT
max(case when Order_Event = "Order_created" then FORMAT_TIMESTAMP ('%R',Timestamp) end)Order_Created,
max(case when Order_Event = "Order_delivered" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Delivered,
max(case when Order_Event = "Order_planned_delivery" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Planned_Delivery,
FROM my_sweet_table
limit 100
What I don't solve, is to get the same result above for each single order ID.
If I do like this, I get an error message saying "SELECT list expression references column Order_ID which is neither grouped nor aggregated at [2:1]"
SELECT
Order_ID,
max(case when Order_Event = "Order_created" then FORMAT_TIMESTAMP ('%R',Timestamp) end)Order_Created,
max(case when Order_Event = "Order_delivered" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Delivered,
max(case when Order_Event = "Order_planned_delivery" then FORMAT_TIMESTAMP ('%R',Timestamp) end) Order_Planned_Delivery,
FROM my_sweet_table
limit 100
This is i similar task but it doesn't helped me: Transpose rows into columns in BigQuery using standard sql
