I'm pulling the following data out of a couple of tables where I'm interested in the status of a couple of RowID's:
SELECT T.DateTime, T2.Node, T2.ID, T.RowID,
CASE
WHEN T.Status like 'StatusA%' THEN '1'
WHEN T.Status like 'StatusB%' THEN '2'
WHEN T.Status like 'StatusC%' THEN '3'
WHEN T.Status like 'StatusD%' THEN '4'
WHEN T.Status like 'StatusE%' THEN '5'
WHEN T.Status like 'StatusF' THEN '6'
ELSE '0'
END AS ActualStatus
FROM Table1 T INNER JOIN Table2 T2 ON T2.ID = T.ID
WHERE (T.RowID = '1' or T.RowId = '2')
and the output looks like
| DateTime | Node | ID | RowID | ActualStatus |
|---|---|---|---|---|
| 28/06/2022 10:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 2 | 1 |
| 28/06/2022 10:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 1 |
| 28/06/2022 11:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 2 | 1 |
| 28/06/2022 11:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 2 |
| 28/06/2022 12:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 2 | 1 |
| 28/06/2022 12:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 1 |
| 28/06/2022 13:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 2 | 3 |
| 28/06/2022 13:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 1 |
| 28/06/2022 14:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 2 | 1 |
| 28/06/2022 14:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 1 |
I want it to look like:
| DateTime | Node | ID | Row1Status | Row2Status |
|---|---|---|---|---|
| 28/06/2022 10:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 1 |
| 28/06/2022 11:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 2 | 1 |
| 28/06/2022 12:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 1 |
| 28/06/2022 13:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 3 |
| 28/06/2022 14:00 | 5201 | 4160cddd-ba27-44b1-b2f1-13c5c8ac379e | 1 | 1 |
But I can't work out how to make that happen. I've tried sub-queries and pivots but nothing works.