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中的⾏转列例⼦详解--场景1：A Ba1a2a3b4b5希望实现如下效果：a1,2,3b4,5create table tmp asselect 'a' A, 1 B from dual union allselect 'a' A, 2 B from dual union allselect 'a' A, 3 B from dual union allselect 'b' A, 4 B from dual union allselect 'b' A, 5 B fromdual;1.⽅法1：listagg--listagg() + group by: 推荐使⽤select a,listagg(b,',') within group (order by b) as c from tmp group bya;--listagg() + over(partition by )select distinct a,listagg(b,',') within group (order by b) over(partition by a) as c fromtmp ;2.wm_concatselecta,to_char(wm_concat(b)) as b from tmp group bya3.sys_connect_by_pathselect a, max(substr(sys_connect_by_path(b, ','), 2)) strfrom (select a, b, row_number() over(partition by a order by b) rn fromtmp)startwith rn = 1connectby rn = prior rn + 1and a =prior agroup bya;4.max+decodeselecta,max(decode(rn, 1, b, null)) ||max(decode(rn, 2, ',' || b, null)) ||max(decode(rn, 3, ',' || b, null)) strfrom (select a,b,row_number() over(partition by a order by b) as rn fromtmp)group byaorder by1;5.row_number()+leadselect a, strbfrom (selecta,row_number()over(partition by a order by b) asrn,b|| lead(',' || b, 1) over(partition by a order by b) ||lead(',' || b, 2) over(partition by a order by b) ||lead(',' || b, 3) over(partition by a order by b) as strfromtmp)where rn = 1order by 1;6.model语句select a, substr(str,2) bfromtmpmodelreturn updated rows partition by(a) dimension by(row_number() over(partition by a order by b) asrn)measures(cast(b as varchar2(20)) as str)rules upsert iterate(3) until(presentv(str[iteration_number+2],1,0) = 0)(str[0] = str[0]||','||str[iteration_number + 1])order by 1;--场景2：no sex004 2002 2002 2003 1002 1希望实现如下效果：c1 c2002 1 2003 1 0004 0 1也就是说按no sex两个字段count⼈数，得到⼆维表。

先来个简单的用法列转行Create table test (name char(10),km char(10),cj int)insert test values('张三','语文',80)insert test values('张三','数学',86)insert test values('张三','英语',75)insert test values('李四','语文',78)insert test values('李四','数学',85)insert test values('李四','英语',78)select name,sum(decode(km,'语文',CJ,0)) 语文,sum(decode(km,'数学',cj,0)) 数学,sum(decode(km,'英语',cj,0)) 英语from test1group by name姓名语文数学英语张三80 86 75李四78 85 78行转列with x as( selectname,sum(decode(km,'语文',CJ,0)) 语文 ,sum(decode(km,'数学',cj,0)) 数学,sum(decode(km,'英语',cj,0)) 英语fromtestgroupbyname)selectname,decode(rn,1, '语文', 2, '数学', 3,'英语') 课程, decode(rn, 1, 语文, 2, 数学, 3,英语) 分数from x, (selectlevel rn from dual connectby1=1andlevel<=3) (from 后面接两个表,是笛卡尔积)多行转字符串这个比较简单，用||或concat 函数可以实现?1 2 3 selectconcat(id,username) str fromapp_userselectid||username str fromapp_user字符串转多列实际上就是拆分字符串的问题，可以使用 substr 、instr 、regexp_substr 函数方式字符串转多行使用union all 函数等方式wm_concat 函数首先让我们来看看这个神奇的函数wm_concat (列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据?1 2 3 4 5 6 7 createtabletest(id number,namevarchar2(20));insertintotest values(1,'a');insertintotest values(1,'b');insertintotest values(1,'c');insertintotest values(2,'d');insertintotest values(2,'e');效果1 : 行转列 ，默认逗号隔开?1 s electwm_concat(name) namefromtest;效果2: 把结果里的逗号替换成"|"?1 s electreplace(wm_concat(name),',','|') fromtest;效果3: 按ID 分组合并name?1 s electid,wm_concat(name) namefromtest groupbyid;sql 语句等同于下面的sql 语句?1 2 3 4 5 6 7 8 9 10 11 -------- 适用范围：8i,9i,10g 及以后版本 （ MAX + DECODE ） selectid, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ','|| name, null)) || max(decode(rn, 3, ','|| name, null)) str from(selectid,name,row_number() over(partition byid orderbyname) asrn fromtest) t groupbyid orderby1;-------- 适用范围：8i,9i,10g 及以后版本 （ ROW_NUMBER + LEAD ） selectid, str from(selectid,row_number() over(partition byid orderbyname) asrn,name|| lead(','|| name, 1) over(partition byid orderbyname) ||lead(','|| name, 2) over(partition byid orderbyname) ||12 13 14 15 lead(','|| name, 3) over(partition byid orderbyname) asstr fromtest) wherern = 1 orderby1;-------- 适用范围：10g 及以后版本 （ MODEL ）selectid, substr(str, 2) str fromtest model returnupdatedrowspartition by(id) dimension by(row_number() over(partition byid orderbyname) asrn)measures (cast(nameasvarchar2(20)) asstr) rules upsertiterate(3) until(presentv(str[iteration_number+2],1,0)=0) (str[0] = str[0] || ','|| str[iteration_number+1])orderby1;-------- 适用范围：8i,9i,10g 及以后版本 （ MAX + DECODE ） selectt.id id,max(substr(sys_connect_by_path(,','),2)) str from(selectid, name, row_number() over(partition byid orderbyname) rn fromtest) tstart withrn = 1 connectbyrn = priorrn + 1 andid = priorid groupbyt.id;</span>懒人扩展用法:案例: 我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat 来让这个需求变简单，假设我的APP_USER 表中有（id,username,password,age ）4个字段。

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：Oracle⾏转列、列转⾏的Sql语句总结例⼦原型：select bkg_num,shpr_cde from CD_XLS_UPLOAD_DETAIL where cd_xls_upload_uuid='392' ;运⾏结果如下：⼀、多字段的拼接将两个或者多个字段拼接成⼀个字段：select bkg_num||shpr_cde from CD_XLS_UPLOAD_DETAIL where cd_xls_upload_uuid='392' ;运⾏结果：⼆、⾏转列将某个字段的多⾏结果，拼接成⼀个字段，获取拼接的字符串【默认逗号隔开】select wm_concat(bkg_num) from CD_XLS_UPLOAD_DETAIL where cd_xls_upload_uuid='392' ;运⾏结果：6098621760,6098621760开拓：如果不想⽤逗号隔开，可以进⾏替换：select replace(wm_concat(bkg_num),',','|') from test; 也可以进⾏分组的拼接：select id,wm_concat(bkg_num) name from test group by id;三、列转⾏原图如下：转成⾏的形式：实现的sql：create table demo(id int,name varchar(20),nums int); ---- 创建表insert into demo values(1, 'apple', 1000);insert into demo values(2, 'apple', 2000);insert into demo values(3, 'apple', 4000);insert into demo values(4, 'orange', 5000);insert into demo values(5, 'orange', 3000);insert into demo values(6, 'grape', 3500);insert into demo values(7, 'mango', 4200);insert into demo values(8, 'mango', 5500);commit;select name, sum(nums) from demo group by name;select * from (select name, nums from demo) pivot(sum(nums) for name in ('apple','orange','grape','mango')); --实现sql注意： pivot（聚合函数 for 列名 in（类型）），其中 in('') 中可以指定别名，in中还可以指定⼦查询，⽐如 select distinct code from customers指定别名如：select * from (select name, nums from demo) pivot(sum(nums) for name in ('apple' 苹果,'orange' 橘⼦,'grape' 葡萄,'mango' 芒果));。

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

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

首先声明一点，有些例子需要如下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 ALLFROM t_col_rowUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row;若空行不需要转换，只需加一个where条件，WHERE COLUMN IS NOT NULL 即可。

Oracle⾏转列、列转⾏的Sql语句总结多⾏转字符串这个⽐较简单，⽤||或concat函数可以实现SQL Code1 2select concat(id,username) str from app_user select id||username str from app_user字符串转多列实际上就是拆分字符串的问题，可以使⽤ substr、instr、regexp_substr函数⽅式字符串转多⾏使⽤union all函数等⽅式wm_concat函数⾸先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显⽰成⼀⾏,接下来上例⼦,看看这个神奇的函数如何应⽤准备测试数据 SQL Code1 2 3 4 5 6create table test(id number,name varchar2(20)); insert into test values(1,'a');insert into test values(1,'b');insert into test values(1,'c');insert into test values(2,'d');insert into test values(2,'e');效果1 : ⾏转列，默认逗号隔开SQL Code1select wm_concat(name) name from test;效果2: 把结果⾥的逗号替换成"|"SQL Code1select replace(wm_concat(name),',','|') from test;效果3: 按ID分组合并nameSQL Code1select id,wm_concat(name) name from test group by id;sql语句等同于下⾯的sql语句:SQL Code1 2 3 4 5 6-------- 适⽤范围：8i,9i,10g及以后版本（ MAX + DECODE ）select id,max(decode(rn, 1, name, null)) ||max(decode(rn, 2, ',' || name, null)) ||max(decode(rn, 3, ',' || name, null)) strfrom (select id,789101112131415161718192021222324252627282930313233343536 name,row_number () over(partition by id order by name) as rnfrom test) tgroup by idorder by 1;-------- 适⽤范围：8i,9i,10g 及以后版本 （ ROW_NUMBER + LEAD ）select id, strfrom (select id,row_number () over(partition by id order by name) as rn,name || lead (',' || name, 1) over(partition by id order by name) ||lead (',' || name, 2) over(partition by id order by name) ||lead (',' || name, 3) over(partition by id order by name) as str from test)where rn = 1order by 1;-------- 适⽤范围：10g 及以后版本 （ MODEL ）select id, substr (str, 2) strfrom test model return updated rows partition by (id) dimension by (row_number ()over(partition by id order by name) as rn) measures(cast (name as varchar2(20)) as str)rules upsert iterate (3) until(presentv(str [ iteration_number + 2 ], 1, 0) = 0)(str [ 0 ] = str [ 0 ] || ',' || str [ iteration_number + 1 ])order by 1;-------- 适⽤范围：8i,9i,10g 及以后版本 （ MAX + DECODE ）select t.id id, max (substr (sys_connect_by_path(, ','), 2)) strfrom (select id, name, row_number () over(partition by id order by name) rnfrom test) tstart with rn = 1connect by rn = prior rn + 1and id = prior idgroup by t.id;懒⼈扩展⽤法:案例: 我要写⼀个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠⼿⼯写太⿇烦了,有没有什么简便的⽅法? 当然有了,看我如果应⽤wm_concat 来让这个需求变简单，假设我的APP_USER 表中有（id,username,password,age ）4个字段。

oraclesql一列转多行最简单的方法在Oracle SQL中，有多种方法可以将一列数据转换为多行。

以下是几种最简单的方法：1.使用UNIONALL操作符：可以使用UNIONALL操作符将多个SELECT语句的结果合并成一个结果集，从而将一列数据转换为多行。

每个SELECT语句都应该只返回一行数据，并且列数和数据类型必须匹配。

例如，假设我们有一个表单名为employees，其中有一个列名为name，包含多个员工的姓名。

我们可以使用以下语句将该列转换为多行：```sqlSELECT name FROM employeesUNIONALLSELECT name FROM employeesUNIONALLSELECT name FROM employees;```这将返回一个包含所有姓名的结果集。

2.使用CONNECTBYLEVEL子句：CONNECTBYLEVEL子句可以用于生成指定的行数，然后通过连接其他表获取相关的数据。

在这种情况下，我们可以使用CONNECTBYLEVEL子句生成多行，然后连接到原始表以获取实际数据。

例如，使用以下语句生成从1到10的数字序列：```sqlSELECTLEVELFROM dualCONNECTBYLEVEL<=10;```然后，我们可以使用该序列连接到原始表中获取实际数据：```sqlSELECT FROM employees eJOIN (SELECT LEVEL AS numFROM dualCONNECTBYLEVEL<=10)lON l.num = e.employee_id;```这将返回一个包含10个姓名的结果集，每个重复10次。

3.使用PIVOT操作：如果我们希望将一列数据转换为多行，并且我们知道有限的可能值，可以使用PIVOT操作。

这要求我们事先知道有多少个可能的值，并且使用CASE语句或PIVOT运算符将每个可能的值转换为一个列。

oracle的行转列函数Oracle的行转列函数是指将一些列的多个行值转换为一行，通常用于将多行数据合并成单行数据，以便于进行数据汇总或者分析。

在Oracle中，行转列的主要方法有使用PIVOT和UNPIVOT函数以及使用CASE语句进行条件判断。

1.使用PIVOT函数PIVOT函数用于将行数据转换为列数据。

它的语法如下：PIVOT聚合函数FOR列名IN('列值1'AS'新列名1','列值2'AS'新列名2',...);例如，假设有一个表student，包含了姓名(name)、课程(course)和分数(score)三个字段，如下所示：name course score-----------------------Alice Math 85Alice Chinese 90Alice English 95Bob Math 80Bob Chinese 75Bob English 85现在我们希望将每个学生的课程及其对应的分数转换为一行数据，可以使用如下的PIVOT语句：SELECT*FROMSELECT name, course, scoreFROM studentPIVOTAVG(score) -- 可以是其他聚合函数，如SUM、MAX等FOR courseIN ('Math' AS Math, 'Chinese' AS Chinese, 'English' AS English);执行该查询后，将得到如下的结果：name Math Chinese English-------------------------------Alice 85 90 95Bob 80 75 852.使用UNPIVOT函数UNPIVOT函数是PIVOT函数的逆操作，用于将列数据转换为行数据。

它的语法如下：UNPIVOT转换列名FOR列名IN(新列名1AS'列值1',新列名2AS'列值2',...);例如，假设有一个表student，包含了姓名(name)、Math、Chinese 和English三个科目的分数，我们希望将这三个科目的分数转换为一列数据，可以使用如下的UNPIVOT语句：SELECT*FROMSELECT name, Math, Chinese, EnglishFROM studentUNPIVOTscoreFOR courseIN (Math AS 'Math', Chinese AS 'Chinese', English AS'English');执行该查询后，将得到如下的结果：name course score---------------------Alice Math 85Alice Chinese 90Alice English 95Bob Math 80Bob Chinese 75Bob English 853.使用CASE语句进行条件判断除了使用PIVOT和UNPIVOT函数外，我们还可以使用CASE语句进行条件判断，实现行转列的功能。