ORACLE 行转列 案例解析

-- 创建 国家城市信息 临时表                  
WITH T_NATION_CITY_INFO AS(
        SELECT '北京'   AS CITY,'中国' AS NATION FROM DUAL UNION ALL
        SELECT '南京'   AS CITY,'中国' AS NATION FROM DUAL UNION ALL
        SELECT '上海'   AS CITY,'中国' AS NATION FROM DUAL UNION ALL
        SELECT '广州'   AS CITY,'中国' AS NATION FROM DUAL UNION ALL
        SELECT '深圳'   AS CITY,'中国' AS NATION FROM DUAL UNION ALL
        SELECT '纽约'   AS CITY,'美国' AS NATION FROM DUAL UNION ALL
        SELECT '洛杉矶'   AS CITY,'美国' AS NATION FROM DUAL UNION ALL
        SELECT '华盛顿' AS CITY,'美国' AS NATION FROM DUAL UNION ALL
        SELECT '东京'   AS CITY,'日本' AS NATION FROM DUAL UNION ALL
        SELECT '北海道'   AS CITY,'日本' AS NATION FROM DUAL UNION ALL
        SELECT '东京'   AS CITY,'日本' AS NATION FROM DUAL UNION ALL
        SELECT '伦敦'   AS CITY,'英国' AS NATION FROM DUAL UNION ALL
        SELECT '爱丁堡'   AS CITY,'英国' AS NATION FROM DUAL UNION ALL
        SELECT '巴黎'   AS CITY,'法国' AS NATION FROM DUAL UNION ALL
        SELECT '巴黎'   AS CITY,'法国' AS NATION FROM DUAL
) 

-- 查询原始表数据

-- SELECT T.*  FROM T_NATION_CITY_INFO T;

要求：按国家分类，展示每个国家的城市信息

 

--  使用 CONCAT_STR 函数 ，可以去重
SELECT T.NATION,CONCAT_STR(DISTINCT T.CITY)  AS CITY  FROM T_NATION_CITY_INFO T GROUP BY T.NATION;

-- 使用 Oracle 10 WM_CONCAT 函数 ，可以去重 ,
--SELECT T.NATION, TO_CHAR(WM_CONCAT(distinct T.CITY))  AS CITY FROM T_NATION_CITY_INFO T GROUP BY T.NATION;


--  使用 Oracle 11  LISTAGG 函数 ，不能去重
--SELECT T.NATION,LISTAGG( T.CITY ,',') WITHIN GROUP(ORDER BY T.CITY)  AS CITY FROM T_NATION_CITY_INFO T GROUP BY T.NATION;