CODEWITHSUNDEEP
sqlgroup-bypivottransposeimpala
iraciv94
iraciv94

Reputation: 840

Impala pivot from column to row, column names disappear

I am kind of new to impala, and to sql in general. I am trying to do some pivot operations in order to start with this table.

Input:

Name table: MyName 

+-----------+---------------------+-----------+
| Column A  | Column B            | Column C  |
+-----------+---------------------+-----------+
| a1        | b1                  | c1        |
| a2        | b2                  | c2        |
| a3        | b3                  | c3        |
+-----------+---------------------+-----------+

And to obtain this other table trasposed, where b1, b2, b3 goes from column to row.

output:

+-----------+---------------------+-----------+
| b1        | b2                  | b3        |
+-----------+---------------------+-----------+
| a1        | a2                  | a3        |
| c1        | c2                  | c3        |
+-----------+---------------------+-----------+

This is the code I came up so far:

select b_column,
       max(case where b_column='b%' then column_a, column_c end) column_a, column_c
  from MyName
  group by b_column;

But it's not working and I am feeling pretty stuck.

Can anyone give me a hint/suggestion on how to solve the issue?

Thanks so much in advance!

Upvotes: 0

Views: 14630

Answers (2)

Mikky
Mikky

Reputation: 11

select b_column,
       max(case when b_column like 'b%' then column_a end) column_a,
       max(case when b_column like 'c%' then column_c end) column_c
  from MyName
  group by b_column;

Upvotes: 1

Keng
Keng

Reputation: 53101

If you are trying to do a pivot in imapla in general, you can't per the 6.1 documentation, PIVOT is not a current functionality.

https://www.cloudera.com/documentation/enterprise/6/6.1/topics/impala_reserved_words.html

Upvotes: 1

Related Questions