您的位置：360文档中心› Oracle列转行和行转列的几种用法

Oracle列转行和行转列的几种用法

合集下载

oracle行列转换总结

oracle行列转换总结
适用范围：8i,9i,10g及以后版本
SELECT id,
MAX(decode(cn, 'c1', cv, NULL)) AS c1,
MAX(decode(cn, 'c2', cv, NULL)) AS c2,
MAX(decode(cn, 'c3', cv, NULL)) AS c3
cv_pair('c3', t_col_row.c3))) t
ORDER BY 1, 2;
2、行转列
CREATE TABLE t_row_col AS
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
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);
适用范围：8i,9i,10g及以后版本
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;

oracle中的行列转换

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中的行转列例子详解

oracle中如何进行行转列,Oracle中的行转列例子详解

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⼈数，得到⼆维表。

Oracle行转列,列转行

Oracle行转列,列转行

先来个简单的用法列转行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实现行转列功能，并使用逗号进行隔开拼接，成为一条数据

oracle实现行转列功能，并使用逗号进行隔开拼接，成为一条数据

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语句总结

Oracle：Oracle行转列、列转行的Sql语句总结

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' 芒果));。

oracle行列转换关系

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

Oracle行转列、列转行的Sql语句总结

Oracle行转列、列转行的Sql语句总结

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一列转多行最简单的方法

oraclesql一列转多行最简单的方法

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的行转列函数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语句进行条件判断，实现行转列的功能。

oracle行转列，列转行函数的使用（listagg，xmlagg）

oracle行转列，列转行函数的使用（listagg，xmlagg）

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转⾃：，转载请注明来源。

Oracle四种列转行的方法

Oracle四种列转行的方法

Oracle四种列转⾏的⽅法1. Oracle⾃带列转⾏函数listagg：实例：with temp as(select 'China' nation ,'Guangzhou' city from dual union allselect 'China' nation ,'Shanghai' city from dual union allselect 'China' nation ,'Beijing' city from dual union allselect 'USA' nation ,'New York' city from dual union allselect 'USA' nation ,'Bostom' city from dual union allselect 'Japan' nation ,'Tokyo' city from dual)--1select nation, listagg(city, ',') within GROUP(order by city) all_citysfrom tempgroup by nation;Result:NATIONALL_CITYSChina Beijing,Guangzhou,ShanghaiJapan TokyoUSA Bostom,New York2. ⾃定义函数实现：1) 定义函数string_agg：CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)RETURN VARCHAR2PARALLEL_ENABLE AGGREGATE USING t_string_agg;2) 定义Type t_string_agg:CREATE OR REPLACE TYPE "T_STRING_AGG" AS OBJECT(g_string VARCHAR2(32767),STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)RETURN NUMBER,MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,value IN VARCHAR2 )RETURN NUMBER,MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,returnValue OUT VARCHAR2,flags IN NUMBER)RETURN NUMBER,MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,ctx2 IN t_string_agg)RETURN NUMBER);3) 定义Type body:CREATE OR REPLACE TYPE BODY "T_STRING_AGG" ISSTATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)RETURN NUMBER ISBEGINsctx := t_string_agg(NULL);RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,value IN VARCHAR2 )RETURN NUMBER ISBEGINSELF.g_string := self.g_string || ',' || value;RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,returnValue OUT VARCHAR2,flags IN NUMBER)RETURN NUMBER ISBEGINreturnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,ctx2 IN t_string_agg)RETURN NUMBER ISBEGINSELF.g_string := SELF.g_string || ',' || ctx2.g_string;RETURN ODCIConst.Success;END;END;4）实例：--2with temp as(select 'China' nation ,'Guangzhou' city from dual union allselect 'China' nation ,'Shanghai' city from dual union allselect 'China' nation ,'Beijing' city from dual union allselect 'USA' nation ,'New York' city from dual union allselect 'USA' nation ,'Bostom' city from dual union allselect 'Japan' nation ,'Tokyo' city from dual)select nation, string_agg(city) all_citys from temp group by nation;Result：NATIONALL_CITYSChina Beijing,Guangzhou,ShanghaiJapan TokyoUSA Bostom,New York以下两种是针对⼤数据的⾏转列：3. 针对⼤数据的⾏转列1：暂存⼊本地XML。

Oracle中行转列，列转行pivot的用法

Oracle中行转列，列转行pivot的用法

Oracle中⾏转列，列转⾏pivot的⽤法测试数据准备--建表--drop table SalesList;create table SalesList(keHu varchar2(20), --客户shangPin varchar2(20), --商品名称salesNum number(8) --销售数量);--插⼊数据declare--谈⼏个客户cursor lr_kh isselect regexp_substr('张三、李四、王五、赵六','[^、]+',1, level) keHu from dualconnect by level<=4;--进点货cursor lr_sp isselect regexp_substr('上⾐、裤⼦、袜⼦、帽⼦','[^、]+',1, level) shangPin from dualconnect by level<=4;begin--循环插⼊for v_kh in lr_kh loopfor v_sp in lr_sp loopinsert into SalesListselect v_kh.keHu, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;end loop;end loop;commit;end;pivot进⾏转换的sql如下：--⾏转列select*from SalesList pivot(max(salesNum) for shangPin in ( --shangPin 即要转成列的字段'上⾐'as上⾐, --max(salesNum) 此处必须为聚合函数，'裤⼦'as裤⼦, --in () 对要转成列的每⼀个值指定⼀个列名'袜⼦'as袜⼦,'帽⼦'as帽⼦))where1=1; --这⾥可以写查询条件，没有可以直接不要where查询结果如下图：希望这个简单的例⼦能够对⼤家有帮助~~~~~~~~。

oracle行转列通用方法

oracle行转列通用方法

oracle行转列通用方法在Oracle数据库中，将行数据转换为列数据通常需要使用一些特定的技术，如PIVOT操作。

然而，Oracle并没有提供一个直接的函数或操作来将行数据直接转换为列数据。

相反，您需要使用CASE语句或DECODE函数结合聚合函数来实现这一目标。

1.确定要转换的行数据和目标列结构。

2.创建一个临时表或子查询，包含要转换的行数据。

3.使用CASE语句或DECODE函数，根据行数据的值生成目标列数据。

4.使用聚合函数（如MAX、MIN、SUM等）对生成的列数据进行汇总。

下面是一个示例，演示如何将行数据转换为列数据：假设我们有一个名为"sales"的表，包含以下行数据：我们希望将产品作为列标题，汇总每个产品的总销售额。

可以使用以下查询实现：SELECT MAX(CASE WHEN product = 'A' THEN amount ELSE NULL END) AS A,MAX(CASE WHEN product = 'B' THEN amount ELSE NULL END) AS B,MAX(CASE WHEN product = 'C' THEN amount ELSE NULL END) AS CFROM sales;上述查询将返回以下结果：在这个例子中，我们使用了CASE语句和MAX聚合函数。

CASE语句根据产品值生成相应的列数据，MAX函数用于汇总每个产品的总销售额。

您可以根据实际情况调整CASE语句的条件和目标列名称。

请注意，这种方法并不是唯一的解决方案，也可以使用其他技术或工具来实现行转列的转换。

上述方法是一种通用的方法，适用于大多数情况。

oracle行转列方法

oracle行转列方法

oracle行转列方法Oracle中的行转列方法是通过使用PIVOT子句来实现的。

该子句允许将SQL查询结果中的行转换为列，从而生成一种新的查询结果格式。

在使用PIVOT子句时，需要指定用于生成新查询结果的列和行，并且必须使用聚合函数来计算每个新列中的值。

例如，以下是一个简单的查询，它使用PIVOT将销售数据按月份转换为列：SELECT *FROM (SELECT *FROM sales)PIVOT (SUM(quantity_sold)FOR month_id IN ( 1 AS jan,2 AS feb,3 AS mar,4 AS apr,5 AS may,6 AS jun,7 AS jul,8 AS aug,9 AS sep,10 AS oct,11 AS nov,12 AS dec));在上面的查询中，使用了SUM聚合函数来计算每个月份的总销售数量。

FOR子句指定要转换为列的列名，并使用AS子句为新列指定别名。

同时，可以使用UNPIVOT子句来将列转换为行。

该子句允许将列名转换为数据行，并将每个列的值作为新行的值。

以下是一个将列转换为行的示例查询：SELECT *FROM (SELECT *FROM salesUNPIVOT (quantity_sold FOR month_name IN ( jan AS 'January',feb AS 'February',mar AS 'March',apr AS 'April',may AS 'May',jun AS 'June',jul AS 'July',aug AS 'August',sep AS 'September',oct AS 'October',nov AS 'November',dec AS 'December')));在上面的查询中，使用了UNPIVOT子句将每个月份的销售数量转换为新行。

oracle行列转换方法汇总

oracle行列转换方法汇总

oracle行列转换方法汇总Oracle是一种关系型数据库管理系统，提供了丰富的行列转换方法，用于对数据进行灵活的处理和分析。

本文将总结和介绍几种常用的Oracle行列转换方法。

一、行列转换概述行列转换是指将数据从行的形式转换为列的形式，或者从列的形式转换为行的形式。

在数据分析和报表生成过程中，行列转换可以方便地对数据进行透视和展示，使数据更加直观和易于理解。

二、使用PIVOT进行行列转换PIVOT是Oracle提供的一种行列转换函数，可以将行数据转换为列。

其基本语法如下：```SELECT *FROM (SELECT 列1, 列2FROM 表名)PIVOT(聚合函数(列2)FOR 列1 IN (值1, 值2, ...))```其中，列1表示要进行行列转换的列，列2表示要进行聚合的列，聚合函数可以是SUM、AVG、COUNT等。

三、使用UNPIVOT进行行列转换UNPIVOT是PIVOT的逆操作，可以将列数据转换为行。

其基本语法如下：```SELECT *FROM (SELECT 列1, 列2FROM 表名)UNPIVOT(列值FOR 列名 IN (列1, 列2, ...))```其中，列1、列2表示要进行行列转换的列，列值表示转换后的值，列名表示转换后的列。

四、使用CASE语句进行行列转换除了使用PIVOT和UNPIVOT函数外，还可以使用CASE语句进行行列转换。

CASE语句可以根据条件对数据进行分类和分组，从而实现行列转换的效果。

其基本语法如下：```SELECT列1,MAX(CASE WHEN 条件1 THEN 列2 END) AS 列名1,MAX(CASE WHEN 条件2 THEN 列2 END) AS 列名2,...FROM 表名GROUP BY 列1```其中，列1表示要进行行列转换的列，条件1、条件2表示根据哪些条件进行分类和分组，列2表示要进行转换的列，列名1、列名2表示转换后的列。

ORACLE行转列（行转１列，行转多列）

ORACLE行转列（行转１列，行转多列）

ORACLE⾏转列（⾏转１列，⾏转多列）在oracle 11g release 2 版本中新增的listagg函数，listagg是⼀个实现字符串聚合的oracle内建函数;listagg(column,'分隔符') within group (order by column) over(partition by column)分隔符可以为空，order by必选项，可以order by null(1)select status, listagg(risk_id, ',') within group (order by risk_id) from rp_risk group by status;以status分组，将risk_id全部合并显⽰在⼀⾏(2)与许多的聚合函数类似，listagg通过加上over()⼦句可以实现分析功能select risk_id, status, listagg(risk_id, ',') within group (order by risk_id) over(partition by status) from rp_risk;选出与当前risk_id在同⼀个部门的所有risk_id并合并字符串(3)listagg聚合的结果列⼤⼩限制在varchar2类型的最⼤值内（⽐如4000);(4)合并字符串也可以⽤wm_concat(column_name),所有版本的oracle都可以⽤这个函数　listagg()是oracle 11g release 2才有；(5)参考链接--listagg(合并多⾏的值为字符串，只⽤⼀列来显⽰)select status, count(*), listagg(risk_id, ',') within group (order by risk_id) from rp_risk group by status;select risk_id, status, listagg(risk_id, ',') within group (order by risk_id) over(partition by status) from rp_risk;select risk.risk_id, listagg(st_name ||','|| officer.first_name, '; ') within group(order by null) from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officer where risk.risk_id = re.risk_idand re.risk_area_id = area.risk_area_id(+)and area.risk_officer_id = officer.risk_officer_id(+)group by risk.risk_id;--pivot(⾏专列，将多⾏的值改为多列显⽰)(for in的那个column,是某个列的值，也就是将某个列的值作为新的列的column,这个column下边的值好像只能来⾃⼀列)select*from(select risk.risk_id, re.risk_area_order, st_name ||','|| officer.first_name fullnamefrom rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officerwhere risk.risk_id = re.risk_idand re.risk_area_id = area.risk_area_id(+)and area.risk_officer_id = officer.risk_officer_id(+) order by risk.risk_id desc, re.risk_area_order)pivot(max(fullname) for risk_area_order in (1 primaryOfficer, 2 addtionalOffcier1, 3 addtionalOffcier2)) order by risk_id desc;--decode(⾏专列，将多⾏的值改为多列显⽰)(decode的那个column,是某个列的值，也就是将某个列的值作为新的列的column,MAX聚集函数也可以⽤sum、min、avg等其他聚集函数替代) select risk_id,--max(decode(risk_area_order, 1, fullname)) primaryOfficer,--max(decode(risk_area_order, 2, fullname)) addtionalOffcier1,--max(decode(risk_area_order, 3, fullname)) addtionalOffcier1min(decode(risk_area_order, 1, fullname)) primaryOfficer,min(decode(risk_area_order, 2, fullname)) addtionalOffcier1,min(decode(risk_area_order, 3, fullname)) addtionalOffcier1from(select risk.risk_id, re.risk_area_order, st_name ||','|| officer.first_name fullname from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officerwhere risk.risk_id = re.risk_idand re.risk_area_id = area.risk_area_id(+)and area.risk_officer_id = officer.risk_officer_id(+) order by risk.risk_id, re.risk_area_order)group by risk_id order by risk_id;参考链接：⽐较全⾯的：关于pivot的：关于wm_concat的：。

Oracle实现行列转换的方法分析

Oracle实现行列转换的方法分析

Oracle实现⾏列转换的⽅法分析本⽂实例讲述了Oracle实现⾏列转换的⽅法。

分享给⼤家供⼤家参考，具体如下：1、固定列数的⾏列转换如：student subject grade--------- ---------- --------student1 语⽂ 80student1 数学 70student1 英语 60student2 语⽂ 90student2 数学 80student2 英语 100……转换为：语⽂数学英语student1 80 70 60student2 90 80 100……语句如下：select student,sum(decode(subject,'语⽂', grade,null)) "语⽂",sum(decode(subject,'数学', grade,null)) "数学",sum(decode(subject,'英语', grade,null)) "英语"from tablegroup by student;2、不定列⾏列转换如：c1 c2--- -----------1 我1 是1 谁2 知2 道3 不……转换为1 我是谁2 知道3 不这⼀类型的转换可以借助于PL/SQL来完成，这⾥给⼀个例⼦CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)RETURN VARCHAR2ISCol_c2 VARCHAR2(4000);BEGINFOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOPCol_c2 := Col_c2||cur.c2;END LOOP;Col_c2 := rtrim(Col_c2,1);RETURN Col_c2;END;select distinct c1 ,get_c2(c1) cc2 from table;或者不⽤pl/sql，利⽤分析函数和 CONNECT_BY 实现：SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAMEFROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1 FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rnFROM t))START WITH rn1 IS NULLCONNECT BY rn1 = PRIOR rnGROUP BY c1;3、列数不固定（交叉表⾏列转置）这种是⽐较⿇烦的⼀种，需要借助pl/sql：原始数据：CLASS1 CALLDATE CALLCOUNT1 2005-08-08 401 2005-08-07 62 2005-08-08 773 2005-08-09 333 2005-08-08 93 2005-08-07 21转置后：CALLDATE CallCount1 CallCount2 CallCount3------------ ---------- ---------- ----------2005-08-09 0 0 332005-08-08 40 77 92005-08-07 6 0 21试验如下：1）. 建⽴测试表和数据CREATE TABLE t(class1 VARCHAR2(2 BYTE),calldate DATE,callcount INTEGER);INSERT INTO t(class1, calldate, callcount)VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);INSERT INTO t(class1, calldate, callcount)VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);INSERT INTO t(class1, calldate, callcount)VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);COMMIT ;2）. 建⽴ref cursor准备输出结果集CREATE OR REPLACE PACKAGE pkg_getrecordISTYPE myrctype IS REF CURSOR;END pkg_getrecord;3）. 建⽴动态sql交叉表函数，输出结果集CREATE OR REPLACE FUNCTION fn_rsRETURN pkg_getrecord.myrctypeISs VARCHAR2 (4000);CURSOR c1 ISSELECT ',sum(case when Class1='|| class1|| ' then CallCount else 0 end)'|| ' "CallCount'|| class1|| '"' c2FROM tGROUP BY class1;r1 c1%ROWTYPE;list_cursor pkg_getrecord.myrctype;BEGINs := 'select CallDate ';OPEN c1;LOOPFETCH c1 INTO r1;EXIT WHEN c1%NOTFOUND;s := s || r1.c2;END LOOP;CLOSE c1;s := s || ' from T group by CallDate order by CallDate desc ';OPEN list_cursor FOR s;RETURN list_cursor;END fn_rs;4）. 测试在sql plus下执⾏：var results refcursor;exec :results := fn_rs;print results;CALLDATE CallCount1 CallCount2 CallCount3--------------- ---------- ---------- ----------2005-08-09 0 0 332005-08-08 40 77 92005-08-07 6 0 21说明:decodeDECODE(value, if1, then1, if2,then2, if3,then3, . . . else )Value 代表某个表的任何类型的任意列或⼀个通过计算所得的任何结果。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Oracle列转行和行转列的几种用法
栏到
栏主要讨论sys_connect_by_path的用法
1，具有分层关系
SQL > createtabledept(deptnononumber，deptname varchar2 (20)，mgrnononumber)；
table created .
SQL >插入deptvalues (1，“总部”，空)；
1 row created .
SQL >插入deptvalues (2，’浙江分公司’，1)；
1 row created .
SQL > insert into dept values(3，’杭州分公司’，2)；已创建
1行。

SQL >提交；
提交完成。

SQL >从部门连接中选择最大值(子串(sys_connect_by_path(deptname，’，’)，2))由先前部门连接= mgrno 最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(DEPTNAME)，’)，2)
-总部，浙江分行，杭州分行
2，行-列转换
如果一个表的所有列都连接到一行，用逗号分隔:
SQL >选择最大值(SUBSTER(SYS _ CONNECT _ BY _ PATH(column _ name，’，’)，2))
MAX(SUBSTRA(SYS _ CONNECT _ BY _ PATH(COLUMN _ NAME，’，’)，2))
- DEPTNO，DEPTNAME，MGRNO
3，ListAgg(Oracle 11g)
SQL >选择DEPTNO，
2 ListAgg(NAME，’；’)
3在组
4内(由搪瓷订购)搪瓷
5来自emp
6组由deptno
7由deptno 8 /
DEPTNO搪瓷
- -
10 CLARK。

国王；米勒
20亚当斯；福特。

琼斯；
SCOTT。

史密斯
30艾伦；布莱克；
JAMES；马丁；
TURNER；下面的W ARD
是使用tempas的列转换1
的两种用法(从t_cc_l2_employee 256中选择account_no，user_party_id，data_hierarchy_id+ where account_no不为空)
从temp union中选择account _ no，user _ party _ id全部
选择account _ no，data _ hierarchy _ id从temp 2 256中选择来自t_cc_l2_employee的data_hierarchy_id，其中account_no不为空，user_party_id不为空，data_hierarchy_id不为空)MODEL
RETURE UPDATED ROWS
PARTITION BY(account _ no)DIMENSION BY(0 AS n) MEASURES(‘ xx ‘ AS cn，’ yyyyyy’ AS cv，user_party_id，data _ hierarchy _ id)RULES UPSERT ALL(
cn[1
-注意:模型语法SQL经常遇到两个问题
1 ora-32638:模型维度中的非唯一寻址(问题是模型结果集中对应于分区依据的列具有重复值)
2 ora-25137数据值超出范围(将“yyyyyyyy”中的“yyyyyyy”扩展几个位置可以解决您的问题)
255。

相关文档
最新文档