Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Transpose columns to rows

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

New Contributor
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
Don't have an account?
Coming from Hortonworks? Activate your account here