I wrote below code to pivot the below given sample table in db2.
Sample_Table
| Column_A | Column_B |
|---|---|
| A | 1 |
| A | 2 |
| B | 1 |
| B | 2 |
Expected Output
| Column_A | Column_B_1 | Column_B_2 |
|---|---|---|
| A | 1 | 2 |
| B | 1 | 2 |
Code:
select Column_A ,
max(case when Column_B = '1' then Column_B end) as Column_B_1 ,
max(case when Column_B = '2' then Column_B end) as Column_B_2
from Sample_Table group by Column_A ;
As you can see that number of columns ie. Column_B_1, Column_B_2 are hardcode. How can I dynamically determine the number columns? This will help me to handle a table which has more or less duplication of data in Column_A than what I have hardcode.
I tried to dynamically generate the SQL using repetitive concatenation within while loop. And then execute that SQL code. But that did not work.