Reply
Highlighted
New Contributor
Posts: 3
Registered: ‎08-20-2018
Accepted Solution

Convert columns to rows

Hi Team,

 

I want to convert columns as rows as per user requirement. I know that this can be done using UNION ALL but I have 60 columns need to be converted. If I go with UNION ALL then I would need to write 60 UNION ALLs which will impact the performance.

Is there any direct way of converting columns to rows like using any functions etc.,

 

Untitled.png

Master
Posts: 430
Registered: ‎07-01-2015

Re: Convert columns to rows

Hi, you dont have to union 60 times,  you can do this:

select 
t.rowid,
t.orderdate,
t.shipmode,
t.customername,
t.state,
m.metric,
case m.metric
when 'sales' then t.sales
when 'quantity' then t.quantity
end value from mytable t
cross join (
select 'sales' metric union all
select 'quantity' metric
) m

 

 

New Contributor
Posts: 3
Registered: ‎08-20-2018

Re: Convert columns to rows

Hello Tomas79,

 

Yes, it looks good.

Since we go with cross join doesn't it effect the performance?

If I have 100000 records then if I go with 10 UNION ALL then the count of records will go to 1million.

Is there any suggestion please to improve the performance with this approach?

Master
Posts: 430
Registered: ‎07-01-2015

Re: Convert columns to rows

You can materialize this metric table with 60 rows, and then Impala will broadcast it to every node (or Hive will run a map-side join) so it will not affect the shuffling over network.
However the result set will be N times larger, but that's the point of the query right? (produce from 100k with 10 metrics a 1M table)