UNPIVOT a presto table

user_17680939 0
Viewed 1218

I am very new to SQL with presto and trying to unpivot a table that looks like this: Pivot to make it look like this: Unpivoted

I have tried using UNPIVOT function, I have tried CROSS JOINING with unnest. I finally thought I had figured it out using UNION but I can quiet get the query right. Could you please push me in the right direction?

This is what I was working on:

SELECT CASE_ID, Account, Amount 
FROM 
TEST_TABLE 
CROSS JOIN UNNEST (
ARRAY['Factor_Cost','Unfactor_cost'],
ARRAY[Factor_Cost,Unfactor_cost]
) t2(account, amount)

Thanks so much, R

presto unpivot sql
1 Answers

You are on the right track. It can be done using cross join + case expression to calculate amount depending on account value:

SELECT t1.CASE_ID, 
       t2.Account,
       case when t2.Account = 'Factor_Cost' then t1.factor_cost else t1.unfactor_cost end  Amount 
FROM 
TEST_TABLE t1 
CROSS JOIN (
values('Factor_Cost'),('Unfactor_cost')
) t2(account)

Cross join will duplicate rows by account values and CASE expression it to take corresponding column from t1 as Amount.

Or the same using CROSS JOIN UNNEST, without CASE expression:

SELECT t1.CASE_ID, 
       t2.Account,
       t2.Amount 
FROM 
TEST_TABLE t1 
CROSS JOIN UNNEST(
ARRAY['Factor_Cost','Unfactor_cost'],
ARRAY[t1.factor_cost,t1.unfactor_cost]
) t2(account,amount)

Or the same using UNION ALL (less efficient approach)

SELECT CASE_ID, 'Factor_Cost' Account, factor_cost Amount from TEST_TABLE t1 
union all
SELECT CASE_ID, 'Unfactor_cost' Account, unfactor_cost Amount from TEST_TABLE t1

All these queries can produce results ordered differently. only using additional ORDER BY can guarantee the same order.

user_27003440
Related