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:
LISTAGG (<expr> [, <delimiter>]) WITHIN GROUP (ORDER BY <oby_expression_list>)
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
NATION | CITYS | |
---|---|---|
1 | CHINA | SHANGHAI,GUANGZHOU,BEIJING |
2 | JAPAN | TOKYO |
3 | USA | NEW YORK,BOSTOM |
作者:SuperRaccoon
出处:http://www.cnblogs.com/Geronimo/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。
推崇原创或用自己的语言加以总结。创造干货,收获干货。