question Re: Convert columns to rows in Archives of Support Questions (Read Only)

Convert columns to rows

Srikar — Fri, 16 Sep 2022 13:37:55 GMT

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

Re: Convert columns to rows

Tomas79 — Mon, 27 Aug 2018 04:33:23 GMT

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

Re: Convert columns to rows

Srikar — Mon, 27 Aug 2018 11:47:23 GMT

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?

Re: Convert columns to rows

Tomas79 — Wed, 29 Aug 2018 10:57:00 GMT

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)