CODEWITHSUNDEEP
sqlpresto
dtsellos03
dtsellos03

Reputation: 43

Generating additional SQL rows from columns

In presto, I have rows of the form

name, var1, var2, var3
Foo, A, B, C

And because I need to group by var1, and also by var2 and var3 (each separately), I want to transform each row into three rows of the form:

name, key
Foo, var1=A
Foo, var2=B
Foo, var3=C

So that I can then just group by key. Presto doesn't have an UNPIVOT function, so any advice would be appreciated!

Upvotes: 1

Views: 211

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

You can increase the number of rows by using a cross join to a subquery of the needed number of rows e.g.

select
      t.name
    , case when n.n = 1 then t.var1
           when n.n = 2 then t.var2
           when n.n = 3 then t.var3
      end as key
from sourcetbl t
cross join (
       select 1 as n union all
       select 2 as n union all
       select 3 as n
       ) n

and then a case expression may be used to reduce the number of columns based on the supplied "row number" (n.n in my example)

Upvotes: 1

Related Questions