【Teradata SQL】行列转换函数PIVOT和UNPIVOT、TD_UNPIVOT

1.行转列函数PIVOT

Pivot是用于将行转换为列的关系运算符。该函数对于报告用途很有用，因为它允许您聚合和旋转数据以创建易于读取的表。在SELECT语句的FROM子句中指定PIVOT运算符。对于可以用包含透视运算符的select查询指定的其他子句没有限制。

（1）语法

 （2）实例

#数据准备
CREATE TABLE star1(
country VARCHAR(20)
,state VARCHAR(10)
, yr INTEGER
,qtr VARCHAR(3)
,sales INTEGER
,cogs INTEGER
);

insert into star1 values('USA','CA',2001,'Q1',30,15);
insert into star1 values('Canada','ON',2001,'Q2', 10, 0);
insert into star1 values('Canada','BC',2001,'Q3', 10 ,0);
insert into star1 values('USA','NY',2001,'Q1',45, 25);
insert into star1 values('USA','CA',2001,'Q2', 50 ,20);

SELECT * FROM star1;

单个转换维度（Quarter）实例，将Q1、Q2、Q3季度的sales和cogs值转换为列，转换效果如下：

#写法一
SELECT *
FROM star1 PIVOT (
SUM(sales) as ss1, SUM(cogs) as sc FOR
qtr
IN ('Q1' AS
Quarter1,
'Q2' AS Quarter2,
'Q3' AS Quarter3)
)Tmp;

#写法二
SELECT country, state,
SUM(case when yr = 2001 and qtr = 'Q1' then sales end) as "2001_q1_ss",
SUM(case when yr = 2001 and qtr = 'Q2' then sales end) as "2001_ q2_ ss",
SUM(case when yr = 2001 and qtr = 'Q3' then sales end) as "2001_q3_ss",
SUM(case when yr = 2001 and qtr = 'Q1' then cogs end) as "2001_ q1_sc",
SUM(case when yr = 2001 and qtr = 'Q2' then cogs end) as "2001_q2_sc",
SUM(case when yr = 2001 and qtr = 'Q3' then cogs end) as "2001_q3_sc"
FROM star1
GROUP BY country, state;

两个转换维度（year、Quarter）实例，将2001年Q1、Q2、Q3季度的sales和cogs值转换为列，转换效果如下：

#写法一（默认按照聚合字段sales、cogs和 FOR列表字段yr、qtr以外的表中字段进行分组，本例中按照country、state字段分组）
SELECT *
FROM star1 PIVOT (
SUM(sales) AS ss1, SUM(cogs) AS sc FOR 
(yr, qtr)
IN ((2001, 'Q1'),
(2001, 'Q2'),
(2001, 'Q3'))
)Tmp;

#写法二
SELECT country, state,
SUM(case when yr = 2001 and qtr = 'Q1' then sales end) as "2001_q1_ss",
SUM(case when yr = 2001 and qtr = 'Q2' then sales end) as "2001_ q2_ ss",
SUM(case when yr = 2001 and qtr = 'Q3' then sales end) as "2001_q3_ss",
SUM(case when yr = 2001 and qtr = 'Q1' then cogs end) as "2001_ q1_sc",
SUM(case when yr = 2001 and qtr = 'Q2' then cogs end) as "2001_q2_sc",
SUM(case when yr = 2001 and qtr = 'Q3' then cogs end) as "2001_q3_sc"
FROM star1
GROUP BY country, state;

先行转列，后列转行

CREATE TABLE t1 (
place CHAR(5)
, sales1 INTEGER
, sales2 INTEGER
,sales3 INTEGER
, sales4 INTEGER
, sales5 INTEGER)
PRIMARY INDEX ( place );


insert  into t1 values('Hyd' ,110 ,100 ,1000 ,1100, 500);
insert  into t1 values('Che', 120 ,200, 2000, 1200, 600);
insert  into t1 values('Kol' ,150 ,500, 5000, 1500, 900 );
insert  into t1 values('Mee', 140, 400, 4000 ,1400 ,800);
insert  into t1 values('Pun', 130 ,300, 3000 ,1300, 700);

SELECT * from (
 SELECT * from t1
 UNPIVOT(
      saleval
      for sales in (sales1, sales2, sales3,sales4, sales5)
  )dt1
)dt2
PIVOT(
SUM(saleval)
for place in ('hyd','Che','pun','mee','kol')
)dt3;

2.列转行函数UNPIVOT

UNPIVOT是透视操作的反向操作。它提供了一种将列转换为行的机制。unpivot功能先前是通过td_unpivot表运算符引入的。此功能引入语法以支持SELECT语句的FROM子句中的UNPIVOT运算符。
注：UNPIVOT在内部调用td_UNPIVOT表运算符。您仍然可以独立于unpivot使用td_unpivot。

（1）语法

（2）实例

#数据准备
CREATE TABLE star1p(
country VARCHAR(20)
,state VARCHAR(20)
,Q101Sales INTEGER
,Q201Sales INTEGER
,Q301Sales INTEGER
,Q101Cogs INTEGER
,Q201Cogs INTEGER
,Q301Cogs INTEGER
);

insert into star1p values('Canada','ON',NULL, 10 ,NULL, NULL, 0, NULL);
insert into star1p values('Canada','BC', NULL, NULL ,10, NULL, NULL, 0);
insert into star1p values('USA','NY', 45, NULL, NULL, 25 ,NULL, NULL);
insert into star1p values('USA','CA', 30 ,50, NULL, 15, 20, NULL);

SELECT * FROM star1p;

将Q101、Q201、Q301的sales和cogs列数据，转换为行数据

SELECT *
FROM star1p UNPIVOT (
   (sales,cogs) 
   FOR yr_qtr IN (
       (Q101Sales, Q101Cogs) AS 'Q101',
       (Q201Sales, Q201Cogs) AS 'Q201',
       (Q301Sales, Q301Cogs) AS 'Q301'
    )
) Tmp;

 将Q101、Q201、Q301的sales和cogs列数据，转换为行数据----包含NULL值

SELECT *
FROM star1p UNPIVOT INCLUDE NULLS (
    (sales,cogs) 
    FOR yr_qtr IN(
      (Q101Sales, Q101Cogs) AS 'Q101'
      , (Q201Sales, Q201Cogs) AS 'Q201'
      , (Q301Sales,Q301Cogs) AS 'Q301'
    )
) Tmp;

将Q101、Q201、Q301的sales和cogs列数据，转换为行数据----不含NULL值

SELECT *
FROM star1p UNPIVOT EXCLUDE NULLS (
    (sales, cogs) 
    FOR yr_qtr IN(
        (Q101Sales, Q101Cogs) AS 'Q101'
        , (Q201Sales, Q201Cogs) AS 'Q201'
        , (Q301Sales,Q301Cogs) AS 'Q301'
    )
) Tmp;