SQL: Convert a Wide Table to Narrow Table

Question

I have a table where for each row there are some boolean fields like this:

user_id          | USD | EUR | CAD | ....
               1 |   1 |   1 |   0 | ....
               2 |   0 |   1 |   1 | ....

and I need to convert it to something like this:

user_id          | currency
               1 | USD
               1 | EUR
               2 | EUR
               2 | CAD

Imanaged to construct an ugly SQL with lots of UNION (one per each currency) but I feel it's not the best way. Help anyone?

P.S. This query will be executed on AWS Athena so I'm concerned about the costs and I'd love the most optimal query for this.

Answer 1

If your database supports lateral join and the values() row constructor, then you can do:

select x.user_id, x.currency
from mytable t
cross join lateral (values(user_id, 'USD', usd), (user_id, 'EUR', eur), (user_id, 'CAD', cad)) x(user_id, currency, val)
where x.val= 1

Some databases implement the lateral join with cross apply instead of cross join lateral.

A more portable approach is union all. This is less efficient since it requires multiple table scans:

select user_id, 'USD'currency from mytable where usd = 1
union all select user_id, 'EUR' from mytable where eur = 1
union all select user_id, 'CAD' from mytable where cad = 1