Transpose columns to rows

[#1]

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

[#2]

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

[#3]

@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

[#4]

Thanks you

@saranvisa

@mathieu.d

[#5]

you can use DECODE or CASE.

[#6]

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