How can I convert a table to pivot(or transpose) table?

Question

I have a table like this: table1:

month       pool_name     apy
------------------------------
04-2021         a          1
04-2021         c          2
04-2021         d          1
04-2021         f          3
04-2021         g          5
04-2021         h          6
05-2021         a          2 
05-2021         b          3
05-2021         c          2
05-2021         e          7
05-2021         f          5
05-2021         h          6
05-2021         i          4
.
.
.

how can I transpose or pivot (actully I don't know about that) the table1 to table2:

month     a    b   c    d    e    f    g    h   i
----------------------------------------------------
04-2021   1    0   2    1    0    3    5    6   0
05-2021   2    3   2    0    7    5    0    6   4

I use this code but I have an error.

select month,pool_name,apy from table1
    pivot (sum(apy) for pool_name in (select distinct(pool_name) from table1))

I have to write this code in snowflake (classic web interface). Thank you in advance.

what RDBMS are you using?, some like MySQL dont support a pivot explicitly while others like PostgreSQL do. — Rinkesh P, Apr 11 '22 at 05:38
I can't really help you, but tags were updated now. Maybe this doc page helps you a bit, but probably you already found it: https://docs.snowflake.com/en/sql-reference/constructs/pivot.html — cyberbrain, Apr 11 '22 at 08:42
@cyberbrain I read this link a thousand times. Now my problem is to make the pivot dynamic. — Piroozman, Apr 11 '22 at 09:17
https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — John Joseph, Apr 13 '22 at 17:21

Pankaj · Answer 1 · 2022-04-13T17:08:08.593

For Dynamic pivot you need to use a procedure or script. Dynamic pivot on table (column - "pool_name"):

Main Query

EXECUTE IMMEDIATE $$
declare 
res resultset;
itm_name varchar2(2000):=null;
col_list varchar2(2000):=null;
c1 cursor for select distinct pool_name from tab_pool order by pool_name desc;
BEGIN
  for rec in c1 do
  itm_name := NVL2(itm_name,concat(',',itm_name),'');
  itm_name := '\''||rec.pool_name||'\''||itm_name;
  col_list := NVL2(col_list,concat(',',col_list),'');
  col_list := 'NVL("\''||rec.pool_name||'\'",0) as '||rec.pool_name||col_list;
  end for;
  res := (execute immediate 'select month,'||col_list||' from tab_pool pivot(min(apy) for pool_name in ('||itm_name||'))');
  return table(res);
 return col_list;
END;
$$
;

Output received -

+---------+---+---+---+---+---+---+---+---+---+
| MONTH   | A | B | C | D | E | F | G | H | I |
|---------+---+---+---+---+---+---+---+---+---|
| 04-2021 | 1 | 0 | 2 | 1 | 0 | 3 | 5 | 6 | 0 |
| 05-2021 | 2 | 3 | 2 | 0 | 7 | 5 | 0 | 6 | 4 |
+---------+---+---+---+---+---+---+---+---+---+

Data-set used -

create temporary table tab_pool 
(month varchar2(20), 
pool_name varchar2(5), 
apy number);

insert into tab_pool values
('04-2021','a',1),
('04-2021','c',2),
('04-2021','d',1),
('04-2021','f',3),
('04-2021','g',5),
('04-2021','h',6),
('05-2021','a',2), 
('05-2021','b',3),
('05-2021','c',2),
('05-2021','e',7),
('05-2021','f',5),
('05-2021','h',6),
('05-2021','i',4);

How can I convert a table to pivot(or transpose) table?

1 Answers1

Main Query