如何将表转换为枢轴（或转置）表？

Question

我有这样的桌子： 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
.
.
.

如何转置或枢轴（Actully我不知道）Table1至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

我使用此代码，但有错误。

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

我必须在Snowflake（经典的Web界面）中编写此代码。 先感谢您。

Answer 1

对于动态枢轴，您需要使用过程或脚本。
表上的动态枢轴（列 - “ pool_name”）：

接收到的主要查询

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;
$
;

输出 -

+---------+---+---+---+---+---+---+---+---+---+
| 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 |
+---------+---+---+---+---+---+---+---+---+---+

使用的数据集 -

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);