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

多行合并为一行的SQL语句 目录  TOC \o 1-3 \h \z \u  HYPERLINK \l _Toc184622384 ? 例一  PAGEREF _Toc184622384 \h 1  HYPERLINK \l _Toc184622385 ? 例二  PAGEREF _Toc184622385 \h 2  HYPERLINK \l _Toc184622386 ? 例三  PAGEREF _Toc184622386 \h 3  HYPERLINK \l _Toc184622387 ? 例四  PAGEREF _Toc184622387 \h 5  HYPERLINK \l _Toc184622388 ? 例五  PAGEREF _Toc184622388 \h 6  HYPERLINK \l _Toc184622389 例六 如何将多行数据合并成一行多列  PAGEREF _Toc184622389 \h 7  HYPERLINK \l _Toc184622390 例七 C#  PAGEREF _Toc184622390 \h 12  例一 表数据：test no?????? 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 部门?????????