oracle 行转列,用特殊符号拼接
第一种
函数 wm_content('待转列名称') 11g 往后,不推荐使用
实例
多表关联时可以使用
(SELECT wm_concat(nvl(B1.OPERA_CODE,'a')) AS OPERA_CODE FROM OPERATION_NAME B1 WHERE T1.OPERA_REQ_NO=B1.OPERA_REQ_NO) AS SURGERY_OPER_CODE
第二种
实例
WITH TEMP AS
(SELECT 'CHINA' NATION, 'GUANGZHOU' CITY
FROM DUAL
UNION ALL
SELECT 'CHINA' NATION, 'SHANGHAI' CITY
FROM DUAL
UNION ALL
SELECT 'CHINA' NATION, 'BEIJING' CITY
FROM DUAL
UNION ALL
SELECT 'USA' NATION, 'NEW YORK' CITY
FROM DUAL
UNION ALL
SELECT 'USA' NATION, 'BOSTOM' CITY
FROM DUAL
UNION ALL
SELECT 'JAPAN' NATION, 'TOKYO' CITY
FROM DUAL)
SELECT NATION, LISTAGG(CITY, ',') WITHIN GROUP(ORDER BY CITY) AS CITIES
FROM TEMP
GROUP BY NATION
用法:
LISTAGG('待转的列','分割符号') WITHIN GROUP(ORDER BY 列字段) AS XXX 字段 FROM 表 WHERE 条件 group by 分组条件 ;