databrickstranspose

How to transpose character data?

I'm trying to transpose in Databricks, when used to PROC SQL in SAS.

This is the data table I am trying to transpose.

This

And I am wanting to get a simple transpose

end result

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...

Solution

  • 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 Result