Support Questions
Find answers, ask questions, and share your expertise

Transpose columns to rows

Explorer

Hey all,

Our production env use Impala 2.2.0, CDH 5.4.3.

 

Does impala has a function to transpose columns to rows?

 

Currently , in order to do so, I need to perform seperate queries, which filter the specific column, and union them.

Because the source table is huge, this solution is not good at all.

Any ideas?

 

Here's a simple example:

 

-- Source table
SELECT * FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp

 

-- Desired output

SELECT a AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp
UNION all
SELECT b AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp
UNION all
SELECT c AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp
UNION all
SELECT d AS selected_value FROM (
SELECT 'a' AS a,'b' AS b,'c' AS c,'d' AS d ) tmp

 

 

Thanks!

Dror

5 REPLIES 5

Re: Transpose columns to rows

Super Collaborator

I don't think Impala has such a feature (but I could be wrong).

 

If I were you, I would try to answer these questions :

- "Why do I need this kind of ouput ?"

- "What do I use it for ?"

- "Can't I acheive my goal with an other output ?"

 

Maybe you will find an other approach more adapted.

 

By the way, I guess something like this would be better (but it will not make a huge difference) :

SELECT a AS col FROM tmp
UNION ALL SELECT b AS col FROM tmp
UNION ALL SELECT c AS col FROM tmp
UNION ALL SELECT d AS col FROM tmp

 

Re: Transpose columns to rows

Champion

@dsss

 

You can find all the Impala build in functions in the below link. I don't find any option for PIVOT (pls double chk)

https://www.cloudera.com/documentation/enterprise/5-5-x/topics/impala_functions.html

 

There are so many ways to transpose row to column/column to row using normal SQL. I would suggest you to follow that and create a UDF (User Defined Function)

https://www.cloudera.com/documentation/enterprise/5-8-x/topics/impala_udf.html

 

Below is the JIRA ticket created with apache to include PIVOT option in Hive, you can see the status & comments. Also some links provided in the comment section to manually transpose the column to row/row to column. 

 

https://issues.apache.org/jira/browse/HIVE-3776

 

Thanks

Kumar

 

Re: Transpose columns to rows

Explorer

Re: Transpose columns to rows

Explorer

you can use DECODE or CASE.

Re: Transpose columns to rows

Explorer
Not helpful yet, but promising... PIVOT keyword is reserved for future use!
https://www.cloudera.com/documentation/enterprise/6/6.2/topics/impala_reserved_words.html