0

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!

iraciv94
  • 782
  • 2
  • 11
  • 26
  • SQL queries have fixed column names. If you want them to come from data values, you need to use dynamic SQL. – Gordon Linoff Jan 25 '19 at 11:40
  • Impala does not support but if you're using a Cloudera plateforme, you can use Hive who does support the pivot / lateral view function – Darko37 Nov 13 '20 at 14:32

2 Answers2

1

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

Keng
  • 52,011
  • 32
  • 81
  • 111
1
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;
Obsidian
  • 3,719
  • 8
  • 17
  • 30
Mikky
  • 11
  • 1
  • Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel Mar 26 '21 at 09:41