CODEWITHSUNDEEP
sqlpivotsubqueryreformat
BaldFeegle
BaldFeegle

Reputation: 57

Is there a way to present row data as columns in SQL

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.

Upvotes: 1

Views: 26

Answers (0)

Related Questions