Oracle行转列,列转行
oracle行列转换总结
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列转行和行转列的几种用法栏到栏主要讨论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，MGRNO3，ListAgg(Oracle 11g)SQL >选择DEPTNO，2 ListAgg(NAME，’；’)3在组4内(由搪瓷订购)搪瓷5来自emp6组由deptno7由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不为空)MODELRETURE UPDATED ROWSPARTITION 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。
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中的⾏转列例⼦详解--场景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行转列公式在Oracle数据库中，要将行转换为列，可以使用PIVOT操作符。
PIVOT操作符可以将行数据转换为列数据，这在需要对数据进行透视分析时非常有用。
下面我将介绍如何使用PIVOT操作符来实现行转列的功能。
首先，假设我们有一个名为EMPLOYEE的表，其中包含员工的姓名、部门和工资信息。
我们想要将部门作为列，员工姓名作为行，显示每个员工在不同部门的工资情况。
下面是一个示例的SQL查询，演示了如何使用PIVOT操作符来实现行转列的功能：sql.SELECT.FROM.(SELECT employee_name, department_name, salary.FROM employee)。
PIVOT.(MAX(salary)。
FOR department_name IN ('部门A' AS A, '部门B' AS B, '部门C' AS C));在这个示例中，首先从EMPLOYEE表中选择员工的姓名、部门和工资信息。
然后使用PIVOT操作符将部门名称转换为列，使用MAX函数来对工资进行聚合。
最后，通过IN子句指定要转换为列的部门名称，并为每个部门指定一个别名。
执行以上查询后，将会得到一个结果集，其中员工姓名作为行，不同部门的工资信息作为列，每一行代表一个员工在不同部门的工资情况。
需要注意的是，PIVOT操作符在Oracle数据库中是比较灵活和强大的，可以根据实际需求进行各种数据透视分析操作。
当然，具体的操作还是要根据实际的数据表结构和需求来进行调整和修改。
希望以上信息能够帮助到你。
oracle的行转列函数
oracle的行转列函数Oracle是一种现代化、高效的数据库管理系统，它在行列转换方面有着强大的转换函数和工具。
行列转换函数是Oracle数据库中的一个重要组成部分，它可以用来将行数据转换为列数据，或将列数据转换为行数据，这在业务分析、数据挖掘等方面都有着极大的用处。
本文将简要介绍Oracle中的行列转换函数。
1. UNPIVOT函数UNPIVOT函数可以将一张带有多个列的表，转换为只有两列的表，其中一列是原来表格的列名，另一列是原来表格这一列的值。
UNPIVOT函数的语法如下：```SELECT *FROM table_name UNPIVOT((value1, 'column1') FOR column1 IN (column2, column3, ...),(value2, 'column2') FOR column2 IN (column3, column4, ...),...);```其中，table_name代表要转换的表格的名称，columnX代表原表格中的列名，valueX代表原表格中的值。
例如，若原表格中有A、B、C、D四个列，包含多行数据，那么可以使用以下语句将其转换为只有两列的表：该语句将生成两列，一列为name，包含了A、B、C、D四个列的名称，另一列为value，包含了相应列的值。
这样就可以方便地进行数据分析了。
该语句将生成一列key，表示原表格中的唯一关键字列，另外还有A、B、C三列，表示原表格中包含的三个列，每行记录表示一个唯一的key值和对应的A、B、C三个列的值。
3. CROSS JOIN函数CROSS JOIN函数可以将两个表中的每一个记录都做一个笛卡尔积，生成一个新表。
例如，若有两个表T1和T2，T1有列A、B，T2有列C、D，可以使用以下语句将它们进行笛卡尔积，生成一个新表：```SELECT *FROM T1CROSS JOIN T2;```该语句将生成一个新表，包含了所有T1和T2中的记录的笛卡尔积。
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列转行和行转列的几种用法
列转行主要讨论sys_connect_by_path的使用方法。
1、带层次关系SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);Table created.SQL> insert into dept values(1,'总公司',null);1 row created.SQL> insert into dept values(2,'浙江分公司',1);1 row created.SQL> insert into dept values(3,'杭州分公司',2);1 row created.SQL> commit;Commit complete.SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from dept connect by prior deptno=mgrno;MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))--------------------------------------------------------------------------------总公司,浙江分公司,杭州分公司2、行列转换如把一个表的所有列连成一行，用逗号分隔:SQL> select max(substr(sys_connect_by_path(column_name,','),2))from (select column_name,rownumrn from user_tab_columns where table_name ='DEPT')start with rn=1 connect by rn=rownum ;MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))-------------------------------------------------------------------------------- DEPTNO,DEPTNAME,MGRNO3、ListAgg(Oracle 11g)SQL> select deptno,2 listagg(ename, '; ' )3 within group4 (order by ename) enames5 from emp6 group by deptno7 order by deptno8 /DEPTNO ENAMES--------- -------------------10 CLARK; KING; MILLER20 ADAMS; FORD; JONES;SCOTT; SMITH30 ALLEN; BLAKE;JAMES; MARTIN;TURNER; WARD下面是列转行的二个用法1with temp as (select account_no, user_party_id, data_hierarchy_id from t_cc_l2_employeewhereaccount_no is not null)selectaccount_no, user_party_id from tempunion allselectaccount_no, data_hierarchy_id from temp2SELECT account_no, cn, cv FROM (select distinct account_no, user_party_id, data_hierarchy_id from t_cc_l2_employeewhereaccount_no is not nullanduser_party_id is not nullanddata_hierarchy_id is not null)MODELRETURN UPDATED ROWSPARTITION 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] = 'c1',cn[2] = 'c2',cv[1] = user_party_id[0],cv[2] = data_hierarchy_id[0])ORDER BY account_no,cn;-- 注意点：model语法SQL经常会遇到二个问题1 ORA-32638: Non unique addressing in MODEL dimensions (问题出在被Model的结果集中的partition by对应的column有重复值)2 ORA-25137 Data value out of range (将'yyyyyy' AS cv 中的'yyyyyy' 扩大几位就可能解决您的问题了)。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
先来个简单的用法列转行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个字段。
查询结果如下?1 2 /** 这里的表名默认区分大小写 */select'create or replace view as select '||wm_concat(column_name) || ' from APP_USER'sqlStrfromuser_tab_columns wheretable_name='APP_USER';利用系统表方式查询?1 s elect* fromuser_tab_columnsOracle 11g 行列互换 pivot 和 unpivot 说明在Oracle 11g 中，Oracle 又增加了2个查询：pivot （列转行） 和unpivot （行转列）参考：/tianlesoftware/article/details/7060306、/technetwork/cn/articles/11g-pivot-101924-zhs.htmlgoogle 一下，网上有一篇比较详细的文档：/display.php?id=506pivot 列转行测试数据 (id ，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。
?1 2 3 4 5 6 7 8 9 createtabledemo(id int,namevarchar(20),nums int);---- 创建表insertintodemo values(1, '苹果', 1000);insertintodemo values(2, '苹果', 2000);insertintodemo values(3, '苹果', 4000);insertintodemo values(4, '橘子', 5000);insertintodemo values(5, '橘子', 3000);insertintodemo values(6, '葡萄', 3500);insertintodemo values(7, '芒果', 4200);insertintodemo values(8, '芒果', 5500);分组查询 （当然这是不符合查询一条数据的要求的）?1 s electname, sum(nums) nums fromdemo groupbyname列转行查询?1 select* from(selectname, nums fromdemo) pivot (sum(nums) fornamein('苹果'苹果, '橘子', '葡萄', '芒果'));注意： pivot （聚合函数 for 列名 in （类型）） ，其中 in(‘’) 中可以指定别名，in 中还可以指定子查询，比如 select distinct code from customers当然也可以不使用pivot 函数，等同于下列语句，只是代码比较长，容易理解?1 2 select* from （selectsum(nums) 苹果 fromdemo wherename='苹果'）,（selectsum(nums) 橘子 fromdemo wherename='橘子'）,（selectsum(nums) 葡萄 fromdemo wherename='葡萄'）,（selectsum(nums) 芒果 fromdemo wherename='芒果'）;unpivot 行转列顾名思义就是将多列转换成1列中去案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。