Oracle行转列LISTAGG函数

工作过程中需要将查询的数据分组并显示在一行。以往的工作经验，在sql server中可以用for xml path来实现。

现提供Oracle数据库的行转列方式

oracle11g官方文档简介如下：

LISTAGG Function

The LISTAGG function orders data within each group based on the ORDER BY clause and then concatenates the values of the measure column. Its syntax is as follows:

expr can be a column, constant, bind variable, or an expression involving them.

delimiter can be a column, constant, bind variable, or an expression involving them.

oby_expression_list can be a list of expressions with optional ordering options to sort in ascending or descending order (ASC or DESC), and to control the sort order of NULLs (NULLS FIRST or NULLS LAST). ASCENDING and NULLS LAST are the defaults.

示例数据：

WITH TEMP AS(  
      SELECT 'CHINA' NATION ,'GUANGZHOU' CITY,2 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'CHINA' NATION ,'SHANGHAI' CITY,1 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'CHINA' NATION ,'BEIJING' CITY,4 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'USA' NATION ,'NEW YORK' CITY,0 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'USA' NATION ,'BOSTOM' CITY,1 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'JAPAN' NATION ,'TOKYO' CITY,1 DISPLAY_ORDER FROM DUAL   
    )  
    SELECT * FROM TEMP

	NATION	CITY	DISPLAY_ORDER
1	CHINA	GUANGZHOU	2
2	CHINA	SHANGHAI	1
3	CHINA	BEIJING	4
4	USA	NEW YORK	0
5	USA	BOSTOM	1
6	JAPAN	TOKYO	1

使用LISTAGG:

WITH TEMP AS(  
      SELECT 'CHINA' NATION ,'GUANGZHOU' CITY,2 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'CHINA' NATION ,'SHANGHAI' CITY,1 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'CHINA' NATION ,'BEIJING' CITY,4 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'USA' NATION ,'NEW YORK' CITY,0 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'USA' NATION ,'BOSTOM' CITY,1 DISPLAY_ORDER FROM DUAL UNION ALL  
      SELECT 'JAPAN' NATION ,'TOKYO' CITY,1 DISPLAY_ORDER FROM DUAL   
    )  
    SELECT NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY DISPLAY_ORDER)  
    FROM TEMP  
    GROUP BY NATION

<?xml version="1.0" ?>

	NATION	CITYS
1	CHINA	SHANGHAI,GUANGZHOU,BEIJING
2	JAPAN	TOKYO
3	USA	NEW YORK,BOSTOM

转载于:https://www.cnblogs.com/Geronimo/p/6399204.html