Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Transpose columns to rows

avatar
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

avatar
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

 

avatar
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

 

avatar
Explorer

avatar
Rising Star

you can use DECODE or CASE.

avatar
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