Oracle经验分享-多行合并一行、行列对调

oracle中的⾏列转换在oracle⽰例数据库scott下执⾏select empno,ename,job,sal,deptno from emporder by deptno,job;--⾏转列--现在查询各部门各⼯种的总薪⽔select deptno, job, sum(sal) total_sal from empgroup by deptno, job order by1, 2;--但是这样不直观,如果能够把每个⼯种作为1列显⽰就会更⼀⽬了然.--这就是需要⾏转列。

--在11g之前，需要⼀点技巧，利⽤decode函数才能完成这个⽬标。

select deptno,sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT_SAL,sum(decode(job, 'MANAGER', sal, 0)) as MANAGER_SAL,sum(decode(job, 'ANALYST', sal, 0)) as ANALYST_SAL,sum(decode(job, 'CLERK', sal, 0)) as CLERK_SAL,sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN_SALfrom emp group by deptno order by1;　select deptno,sum(case when job='PRESIDENT'then sal else0end) as PRESIDENT_SAL,sum(case when job='MANAGER'then sal else0end) as MANAGER_SAL,sum(case when job='ANALYST'then sal else0end) as ANALYST_SAL,sum(case when job='CLERK'then sal else0end) as CLERK_SAL,sum(case when job='SALESMAN'then sal else0end) as SALESMAN_SALfrom emp group by deptno order by1;　--如果要在变回前⾯的结果，需要⽤到笛卡尔乘积，⼀⾏变五⾏，然后利⽤decode。

oracle查询多⾏数据合并成⼀⾏数据如果是oracle 10g以上⼀个wm_concat函数就可以解决。

如果是oracle 9i以下就有点⿇烦了。

表结构如下：NAME Null Type------------------------ --------- -----N_SEC_CODE NOT NULL CHAR(6)C_RESEARCHER_CODE NOT NULL VARCHAR2(20)此表保存了“股票”与“研究员”的对应关系数据，⼀般⽽⾔，对于同⼀只股票⽽⾔，可能有多个研究员对其进⾏跟踪研究。

所以⽬前遇到⼀个要求，需查询出每只股票和对应的研究员（研究员代码间，使⽤逗号分隔）。

例如有如下数据：000297 chenpeng000297 liusu合并处理后需显⽰为：000297 chenpeng,liusu⽹上查了很多⽅法，但通常⽽⾔都是编写⾃定义多⾏⽂本合并函数，或者对⽀持的列数具有局限性。

最后在英⽂google中搜到如下⽐较巧的⽅法。

不⽤在数据库中增加function，纯SQL⼀次性搞定，⽽且扩充性很好，没有列的限制。

CodeSELECT n_sec_code, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherListFROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,lvl DESC) rn,n_sec_code, textFROM (SELECT n_sec_code, LEVEL lvl,SYS_CONNECT_BY_PATH (c_researcher_code,'/') textFROM (SELECT n_sec_code, c_researcher_code as c_researcher_code,ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,c_researcher_code) xFROM m_researcher_stock_relORDER BY n_sec_code, c_researcher_code) aCONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x))WHERE rn = 1ORDER BY n_sec_code;预想的结果成功出现，多⾏数据成功汇总到⼀⾏，特此分享与⼤家。

oracle实现⾏转列功能，并使⽤逗号进⾏隔开拼接，成为⼀条数据有两种⽅式1、第⼀种：使⽤WM_CONCAT函数，不过这个函数已经被oracle弃⽤了，不建议使⽤，如果数据库中还有这个函数也可以使⽤select sfc_no,wm_concat(mark_operation_id) from bp_marking where create_date>sysdate-1/24group by sfc_no简单说⼀下就是查询bp_marking表中的sfc_no与对应的所有的mark_operation_id的字段，并且合并到⼀列中结果显⽰如下：实现去重：就是把重复的去掉直接加⼀个distinct即可select sfc_no,wm_concat(distinct mark_operation_id) from bp_marking where create_date>sysdate-1/24group by sfc_no如果没有这个函数也想添加的话，可以试⼀下如下的⽅法（具体是否能⽤我没试过）2、第⼆种：使⽤LISTAGG函数select sfc_no,LISTAGG(mark_operation_id,',') within group (order by mark_operation_id) from bp_marking where create_date>sysdate-1/24group by sfc_no结果跟上⾯的结果是⼀样的。

如何实现去重：把表再嵌套⼀层即可。

即先把重复的数据去掉，然后再对这个表进⾏listagg操作。

select sfc_no,LISTAGG(mark_operation_id,',') within group (order by mark_operation_id) from (select distinct sfc_no,mark_operation_id from bp_marking where create_date 执⾏完之后有时候会显⽰字符串连接过长的问题，因为listagg设定的字符串长度只有4000，超过4000就会报错。

用ORACLE分析函数实现行列转换黄莉玉素甫·艾白都拉（新疆师范大学数理信息学院，新疆830054）摘要对数据库中的数据用SQL实现行列转换，不但需要编写复杂的程序代码，还需要编写存储过程。

若引入ORACLE中的分析函数则会使该过程简便很多。

首先找出表中所有关键字的属性个数的最大值，设为n，其次为每个关键字新添加n列，并用分析函数查询关键字的属性所处列的位置，然后将每个关键字的多行属性转换成多列属性，最后把生成的多个新列拼成一个串形成一列，从而实现行列转换。

关键字Oracle数据库；分析函数；行列转换1 引言分析函数的设计目的是为了解决诸如“累计计算”等问题。

虽然大部分的问题都可以用PL/SQL解决，但是性能并不理想，首先查询本身并不容易编写，其次有些很难在SQL 中直接做的查询但实际上是很普通的操作，比如实现数据表中行列传换。

这样的问题在SQL 中做查询就很困难。

在分析函数出现以前，我们必须使用自联查询或者子查询甚至复杂的存储过程实现的语句，现在只要一条简单的SQL语句就可以实现了，而且在执行效率方面也有相当大的提高。

本文将以一个实例来描述如何采用分析函数实现数据中的行列互换。

２原理2.1 分析函数的格式及语法分析函数是在一个记录行分组的基础上计算它们的总值。

行的分组被称窗口，并通过分析语句定义。

对于每记录行，定义了一个“滑动”窗口。

该窗口确定“当前行”计算的范围。

窗口的大小可由各行的实际编号或由时间等逻辑间隔确定。

分析函数以如下形式开头：Analytic-Function（<Argument>，<Argument>，...）OVER （<Query-Partition-Clause><Order-By-Clause><Windowing-Clause>）（1）Analytic-Function：分析函数的名称，Oracle10gR2带的内置分析函数有多个，包括：AVG、CORR、COVAR_POP、COVAR_SAMP、COUNT、LAG、LAST、LEAD、MAX、MIN、RANK、SUM等；对于用户自定义的分析函数，分析函数名称需要满足标识符规则。

oracle行列转换总结最近论坛很多人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，希望对大家有所帮助，同时有何错疏，恳请大家指出，我也是在写作过程中学习，算是一起和大家学习吧。

行列转换包括以下六种情况：*列转行*行转列*多列转换成字符串*多行转换成字符串*字符串转换成多列*字符串转换成多行下面分别进行举例介绍。

首先声明一点，有些例子需要如下10g及以后才有的知识：a。

掌握model子句，b。

正则表达式c。

加强的层次查询讨论的适用范围只包括8i,9i,10g及以后版本。

begin：1、列转行CREATE TABLE t_col_row(ID INT,c1 VARCHAR2(10),c2 VARCHAR2(10),c3 VARCHAR2(10));INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);COMMIT;SELECT * FROM t_col_row;1)UNION ALL适用范围：8i,9i,10g及以后版本SELECT id, 'c1' cn, c1 cvFROM t_col_rowUNION ALLSELECT id, 'c2' cn, c2 cvFROM t_col_rowUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row;若空行不需要转换，只需加一个where条件，WHERE COLUMN IS NOT NULL 即可。

oracle多行变一行今天遇到将多行转为一行的一个操作，多谢oracle开发板的wildwave 提供了比较通用的解决办法，同时也将自己搜到的这方面资料整理如下，多是用于连接列值的。

String集聚连接技术需要将多行转换为一行，例子如下：基础数据:DEPTNO ENAME---------- ----------20 SMITH30 ALLEN30 WARD20 JONES30 MARTIN30 BLAKE10 CLARK20 SCOTT10 KING30 TURNER20 ADAMS30 JAMES20 FORD10 MILLER预期输出:DEPTNO EMPLOYEES---------- --------------------------------------------------10 CLARK,KING,MILLER20 SMITH,FORD,ADAMS,SCOTT,JONES30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD* LISTAGG分析函数（11g Release 2）* WM_CONCAT内建函数* 自定义函数* 使用Ref Cursor实现通用函数* 用户自定义聚集函数* ROW_NUMBER()和SYS_CONNECT_BY_PATH函数（Oracle 9i）* COLLECT函数（Oracle 10g）LISTAGG分析函数（11g Release 2）Oracle 11g Release 2介绍了LISTAGG 函数，使得聚集连接字符串变得很容易。

并且允许使用我们指定连接串中的字段顺序。

使用LISTAGG如下：COLUMN employees FORMAT A50SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employeesFROM empGROUP BY deptno;DEPTNO EMPLOYEES---------- --------------------------------------------------10 CLARK,KING,MILLER20 ADAMS,FORD,JONES,SCOTT,SMITH30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.WM_CONCAT内建函数如果你的Oracle不是11g Release 2，但是支持WM_CONCAT 函数，那么解决上面的问题同样是小菜一碟，使用WM_CONCAT 函数G如下：COLUMN employees FORMAT A50SELECT deptno, wm_concat(ename) AS employeesFROM empGROUP BY deptno;DEPTNO EMPLOYEES---------- --------------------------------------------------10 CLARK,KING,MILLER20 SMITH,FORD,ADAMS,SCOTT,JONES30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD3 rows selected.自定义函数另一个方法是自定义一个函数解决问题。

Oracle多⾏记录合并连接聚合字符串的⼏种⽅法什么是合并多⾏字符串（连接字符串）呢，例如：SQL> desc test;Name Type Nullable Default Comments------- ------------ -------- ------- --------COUNTRY VARCHAR2(20) YCITY VARCHAR2(20) YSQL> select * from test;COUNTRY CITY-------------------- --------------------中国台北中国⾹港中国上海⽇本东京⽇本⼤阪要求得到如下结果集：------- --------------------中国台北,⾹港,上海⽇本东京，⼤阪实际就是对字符实现⼀个聚合功能，我很奇怪为什么Oracle没有提供官⽅的聚合函数来实现它呢：）下⾯就对⼏种经常提及的解决⽅案进⾏分析（有⼀个评测标准最⾼★★★★★）：1.被集合字段范围⼩且固定型灵活性★性能★★★★难度★这种⽅法的原理在于你已经知道CITY字段的值有⼏种，且还不算太多，如果太多这个SQL就会相当的长。

看例⼦：SQL> select t.country,2 MAX(decode(t.city,'台北',t.city||',',NULL)) ||3 MAX(decode(t.city,'⾹港',t.city||',',NULL))||4 MAX(decode(t.city,'上海',t.city||',',NULL))||5 MAX(decode(t.city,'东京',t.city||',',NULL))||6 MAX(decode(t.city,'⼤阪',t.city||',',NULL))7 from test t GROUP BY t.country8 /COUNTRY MAX(DECODE(T.CITY,'台北',T.CIT-------------------- ------------------------------中国台北,⾹港,上海,⽇本东京,⼤阪,⼤家⼀看，估计就明⽩了（如果不明⽩，好好补习MAX DECODE和分组）。

oracle⾏转列，列转⾏函数的使⽤（listagg，xmlagg）⼀、⾏转列listagg函数：场景：这⾥的表数据使⽤的是oracle默认的scott账户下的emp（员⼯）表。

规范写法 : LISTAGG(字段, 连接符) WITHIN GROUP (ORDER BY 字段)通常情况下，LISTAGG是满⾜需要的，LISTAGG 返回的是⼀个varchar2类型的数据，最⼤字节长度为4000。

所以，在实际开发中，我们可能会遇到⼀个问题，连接长度过长。

在这个时候，我们需要将LISTAGG函数改成XMLAGG函数。

XMLAGG返回的类型为CLOB，最⼤字节长度为32767。

LISTAGG例⼦：1、使⽤条件查询部门号为20号的员⼯：-- 查询部门为20的员⼯列表SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO ='20' ;2、使⽤listagg() WITH GROUP()将多⾏合并成⼀⾏（⽐较常⽤）SELECTT.DEPTNO,listagg (T.ENAME, ',') WITHIN GROUP (ORDER BY T.ENAME) namesFROMSCOTT.EMP TWHERET.DEPTNO ='20'GROUP BYT.DEPTNO3、使⽤listagg() width group() over将多⾏记录在⼀⾏显⽰（不常⽤）SELECTT .DEPTNO,listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) over(PARTITION BY T .DEPTNO)FROMSCOTT.EMP TWHERET .DEPTNO ='20'⼆、XMLAGG函数的例⼦：XMLAGG(XMLPARSE(CONTENT BSO.ID || ',' WELLFORMED) ORDER BY BSO.ID).GETCLOBVAL()规划写法： XMLAGG(XMLPARSE(CONTENT 字段 || 字符串 WELLFORMED) ORDER BY 字段).GETCLOBVAL()三、对于mysql相同的效果实现，可以使⽤group_concat() 函数，详情可参考：mysql相同效果的实现 https:///sinat_36257389/article/details/95052001PostgreSQL 相同效果的实现 https:///sinat_36257389/article/details/95611686转⾃：，转载请注明来源。

多行合并为一行的 SQL 语句目录例一 ...................................................................... 1 例二 ....................................................................... 2 例三 ....................................................................... 3 例四 ....................................................................... 5 例五 .. (6)例六如何将多行数据合并成一行多列 ................................................. 7 例七C# (12)例一表数据：testno q1 n1n2 n3 n4 n5 t1 t2 t3 t4 t5 t6 m1语句：with test as (SELECT 1 AS No, 'N1' AS q FROM Dual UNION ALL1 1 1 1 3 3 3 3 3 3 2SELECT 1 AS No, 'N2' AS q FROM D ual UNION ALLSELECT 1 AS No, 'N3' AS q FROM D ual UNION ALLSELECT 1 AS No, 'N4' AS q FROM D ual UNION ALLSELECT 1 AS No, 'N5' AS q FROM D ual 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 JgFROM (SELECT No, MAX(Sys_Connect_By_Path(q, ',')) AS Jg FROM (SELECT No, q, Row_Number() Over(PARTITION BY No ORDER BY No, q) RnFROM 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 ||T||(Rn - 1) = PRIOR (NO||T||Rn )”结果才对。