工作过程中需要将查询的数据分组并显示在一行。以往的工作经验，在sql server中可以用for xml path来实现。
现提供Oracle数据库的行转列方式
oracle11g官方文档简介如下：
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 |
原文：http://www.cnblogs.com/Geronimo/p/6399204.html