多行合并为一行的SQL语句.docx

多行合并为一行的SQL语句例一表数据：testno?????? q------------1? ? ? ? n11? ? ? ? n21? ? ? ? n31? ? ? ? n41? ? ? ? n53? ? ? ? t13? ? ? ? t23? ? ? ? t33? ? ? ? t43? ? ? ? t53? ? ? ? t62? ? ? ? m1语句：with test as ( SELECT 1 AS No, N1 AS q FROM Dual UNION ALLSELECT 1 AS No, N2 AS q FROM Dual UNION ALLSELECT 1 AS No, N3 AS q FROM Dual UNION ALLSELECT 1 AS No, N4 AS q FROM Dual UNION ALLSELECT 1 AS No, N5 AS q FROM Dual UNION ALLSELECT 3 AS No, T1 AS q FROM Dual UNION ALLSELECT 3 AS No, T2 AS q FROM Dual UNION ALLSELECT 3 AS No, T3 AS q FROM Dual UNION ALLSELECT 3 AS No, T4 AS q FROM Dual UNION ALLSELECT 3 AS No, T5 AS q FROM Dual UNION ALLSELECT 3 AS No, T6 AS q FROM Dual UNION ALLSELECT 2 AS No, M1 AS q FROM Dual) SELECT No, Substr(Jg, 2, Length(Jg)) AS Jg? FROM (SELECT No, MAX(Sys_Connect_By_Path(q, ,)) AS Jg????????? FROM (SELECT No,?????????????????????? q,?????????????????????? Row_Number() Over(PARTITION BY No ORDER BY No, q) Rn????????????????? FROM Test)???????? START WITH Rn = 1??????? CONNECT BY Rn - 1 = PRIOR Rn?????????????? AND No = No???????? GROUP BY No)谭工前面那个示例CONNECT BY Rn - 1 = PRIOR Rn AND No = No也应改成CONNECT BY No|||||(Rn - 1) = PRIOR (NO|||||Rn)结果才对。另外这个SQL只适合在小数据集或索引结果中进行处理，不然要使用全表扫描效率不高。输出：1?,N1,N2,N3,N4,N52?,M13?,T1,T2,T3,T4,T5,T6例二另解：人员所属部门:第一个部门为缺省部门SELECT 人员编号,?????? MIN(姓名) AS 姓名,?????? Substr(MAX(To_Char(Length(部门), || 部门), 11, 2000) AS 部门? FROM (SELECT 人员编号, 姓名, Sys_Connect_By_Path(部门, ,) AS 部门????????? FROM (SELECT 姓名,?????????????????????? 部门,?????????????????????? 缺省,?????????????????????? 人员编号,?????????????????????? Row_Number() Over(PARTITION BY 姓名 ORDER BY 人员编号, 缺省 DESC) Rn????????????????? FROM (SELECT c.姓名,?????????????????????????????? b.名称 AS 部门,?????????????????????????????? a.缺省,?????????????????????????????? c.编号 AS 人员编号,?????????????????????????????? b.编码 AS 部门编码????????????????????????? FROM 部门人员 a, 部门表 b, 人员表 c???????????????????????? WHERE a.部门id = b.Id?????????????????????????? AND a.人员id = c.Id??????????????????????? ) t)????????