Trafodion 使用PIVOT函数实现行列转换

在前面的文章https://blog.csdn.net/post_yuan/article/details/54845987中，我们介绍了Trafodion的group_concat函数，可以用于把多列转成一行（根据某种分隔符）。
本文我们介绍另一函数PIVOT，PIVOT是另一个行列转换的利器。以下我们简单模拟使用PIVOT的例子，
首先，创建测试表并插入测试数据，

CREATE TABLE salesorder  
(  
    shop VARCHAR(50),  
    product VARCHAR(50),  
    account BIGINT  
);
INSERT INTO salesorder  
SELECT  'ShopA','A',round(100+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopA','A',round(100+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopA','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopA','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopA','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopA','C',round(1000+100*rand(),0)FROM DUAL UNION ALL  
SELECT  'ShopB','A',round(100+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopB','A',round(100+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopB','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopB','C',round(1000+100*rand(),0)FROM DUAL UNION ALL  
SELECT  'ShopB','C',round(1000+100*rand(),0)FROM DUAL UNION ALL  
SELECT  'ShopC','A',round(100+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopC','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopC','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopC','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopC','B',round(200+100*rand(),0) FROM DUAL UNION ALL  
SELECT  'ShopC','C',round(1000+100*rand(),0)FROM DUAL UNION ALL  
SELECT  'ShopC','C',round(1000+100*rand(),0)FROM DUAL ;

查询表数据，数据成功插入，

SQL>SELECT * FROM salesorder;

SHOP                                               PRODUCT                                            ACCOUNT             
-------------------------------------------------- -------------------------------------------------- --------------------
ShopC                                              C                                                          125725026600
ShopC                                              C                                                          125807380900
ShopC                                              B                                                          175364500300
ShopC                                              B                                                          206319632900
ShopC                                              B                                                          210124737700
ShopC                                              B                                                             126031200
ShopC                                              A                                                            3497515300
ShopB                                              C                                                            6801772400
ShopB                                              C                                                           10611919300
ShopB                                              B                                                           14312819900
ShopB                                              A                                                           18480955800
ShopB                                              A                                                           21334784400
ShopA                                              C                                                           24344919000
ShopA                                              B                                                           27237402900
ShopA                                              B                                                           30129887600
ShopA                                              B                                                           32928253100
ShopA                                              A                                                           35958555100
ShopA                                              A                                                           36029144500

现在我们尝试使用PIVOT函数来查询shop为ShopA的product的值有哪些，

SQL>SELECT PIVOT(product) FROM salesorder WHERE shop = 'ShopA';

(EXPR)
--------------------------------------------------------------------------------------------------------------------------------
C,B,B,B,A,A                                                                                                                     

--- 1 row(s) selected.
SQL>SELECT shop,PIVOT(product) as product FROM salesorder WHERE shop = 'ShopA' group by shop;

SHOP                                               PRODUCT                                                                                                                         
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
ShopA                                              B,B,B,A,A,C                                                                                                                     

--- 1 row(s) selected.

可以看出，PIVOT十分的好用，PIVOT属于聚合函数，类似于COUNT、SUM、AVG这种，只不过它是把函数中的值做了拼接，默认以逗号分隔。下面这条语句指定分隔符输出，

SQL>SELECT shop,PIVOT(product, delimiter '-') as product FROM salesorder WHERE shop = 'ShopA' group by shop;

SHOP                                               PRODUCT                                                                                                                         
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
ShopA                                              B-B-B-A-A-C                                                                                                                     

--- 1 row(s) selected.