I'm trying to transpose in Databricks, when used to PROC SQL in SAS.
This is the data table I am trying to transpose.
And I am wanting to get a simple transpose
I am not sure where to start... Seems the Pivot table needs a summarize function etc. This should be really simple...
I have tried adding a sequence counter, that isn't easy with databricks either by id_request. I have tried to replace letters with numbers... Just not working...
Using PIVOT
SELECT *
FROM(
SELECT id_request, Alert_Outcome, ROW_NUMBER() OVER(PARTITION BY id_request ORDER BY Alert_Outcome) AS rn
FROM Test
)
PIVOT (MAX(Alert_Outcome) FOR rn IN (1 AS Alert1, 2 AS Alert2, 3 AS Alert3))
ORDER BY id_request;
Using Conditional Aggregation
SELECT
id_request,
MAX(CASE WHEN rn = 1 THEN Alert_Outcome END) AS Alert1,
MAX(CASE WHEN rn = 2 THEN Alert_Outcome END) AS Alert2,
MAX(CASE WHEN rn = 3 THEN Alert_Outcome END) AS Alert3
FROM(
SELECT id_request, Alert_Outcome, ROW_NUMBER() OVER(PARTITION BY id_request ORDER BY Alert_Outcome) AS rn
FROM Test
) AS D
GROUP BY id_request
ORDER BY id_request
The result is below
