Oracle列转行函数Listagg以及pivot查询示例

简单的Oracle列转行函数Listagg示例：

CREATE TABLE tbl_test
(catalog VARCHAR(1),product VARCHAR(2),amount NUMBER);

INSERT INTO tbl_test VALUES('A','A1',1);
INSERT INTO tbl_test VALUES('A','A1',2);
INSERT INTO tbl_test VALUES('B','B1',3);
INSERT INTO tbl_test VALUES('B','B2',4);
INSERT INTO tbl_test VALUES('B','B2',5);
INSERT INTO tbl_test VALUES('C','C1',6);
INSERT INTO tbl_test VALUES('C','C1',7);
INSERT INTO tbl_test VALUES('C','C2',8);
INSERT INTO tbl_test VALUES('C','C2',9);
COMMIT;

SELECT * FROM tbl_test;

CATALOG PRODUCT     AMOUNT
------- ------- ----------
A       A1               1 
A       A1               2 
B       B1               3 
B       B2               4 
B       B2               5 
C       C1               6 
C       C1               7 
C       C2               8 
C       C2               9 

 9 rows selected 

SELECT
LISTAGG(product,',') WITHIN GROUP( ORDER BY amount) AS lst
FROM tbl_test;

LST    
----------------------------
A1,A1,B1,B2,B2,C1,C1,C2,C2 

SELECT
catalog, LISTAGG(product,',') WITHIN GROUP( ORDER BY amount) as lst
FROM tbl_test
GROUP BY catalog;

CATALOG LST  
------- -----------------
A       A1,A1  
B       B1,B2,B2  
C       C1,C1,C2,C2  

SELECT
LISTAGG(product||'-'||amount,',') WITHIN GROUP( ORDER BY amount) over (partition by catalog)
AS lst
FROM tbl_test
;

LST  
----------------------
A1-1,A1-2  
A1-1,A1-2  
B1-3,B2-4,B2-5  
B1-3,B2-4,B2-5  
B1-3,B2-4,B2-5 
C1-6,C1-7,C2-8,C2-9   
C1-6,C1-7,C2-8,C2-9  
C1-6,C1-7,C2-8,C2-9  
C1-6,C1-7,C2-8,C2-9  

 9 rows selected 

SELECT * FROM tbl_test
pivot (
       sum(amount)
			 FOR(catalog)
			 IN('A','B','C')
)
ORDER BY 1;

PRODUCT        'A'        'B'        'C'
------- ---------- ---------- ----------
A1               3                       
B1                          3            
B2                          9            
C1                                    13 
C2                                    17 

SELECT product,EXTRACT(catalog_xml,'//column[@name="SUM(AMOUNT)"]/text()') AS sum
FROM tbl_test
pivot xml(
       sum(amount)
			 FOR(catalog)
			 IN(ANY)
)
ORDER BY 1;

PRODUCT SUM    
------- -------
A1      3      
B1      3      
B2      9      
C1      13     
C2      17     

转载于:https://www.cnblogs.com/pekkle/p/6568752.html