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列转行和行转列的几种用法栏到栏主要讨论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' 扩大几位就可能解决您的问题了)。

Oracle专题-11G列转行

Oracle 专题-11G 列转行一、列转行上期讲述了行转列 PIVOT，本期讲述它的反向操作-列转行 Unpivot。

假设有一个显示交叉表报表的电子表格（cust_matrix），如下所示： Puchase_Frequency New_York Conn New_Jersey Florida 1 2 3 4 ... and so on 33048 33151 32978 33109 ... 165 0 179 0 173 0 173 0 0 0 0 1 Missouri 0 0 0 0这是数据在电子表格中的存储方式：每个州是表中的一个列（如 New_York、 Conn 等等）。

要转化为如下结构的数据表 customers（行仅显示州代码和该州的购物人数）： Puchase_Frequency STATE_CODE STATE_COUNTS ----------------- ---------- -----------1 Conn 165 1 Florida 0 1 Missouri 0 1 New_Jersey 0 1 New_York 33048 2 Conn 179 2 Florida 0 2 Missouri 0 ... and so on ... 可以使用 DECODE 编写一个复杂 SQL 脚本，将数据加载到 CUSTOMERS 表中。

或者，可以使用 pivot 的反向操作 UNPIVOT，将列打乱变为行，这在 Oracle 数据库 11g 中可以实现。

通过 unpivot 操作可以达到此目的，如下所示： SELECT * FROM cust_matrix unpivot(state_counts FOR state_code IN(New_York,Conn,New_Jersey,Florida,Missouri)) ORDER BY Puchase_Frequency, state_code;注意每个列名如何变为 STATE_CODE 列中的一个值。

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）⼀、⾏转列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四种列转⾏的⽅法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的⽤法测试数据准备--建表--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 中，可以使用 `PIVOT` 关键字来实现行转列的功能。

以下是一个示例 SQL 查询，将表中的行数据转换为列数据：```sqlSELECT *FROM (SELECT employee_id, project_idFROM employee_projects)PIVOT (COUNT(project_id)FOR project_id IN (1 AS project_1, 2 AS project_2, 3 AS project_3));```在这个例子中，`employee_projects` 是原始表名，其中包含 `employee_id` 和`project_id` 列。

上面的查询将根据 `project_id` 列的值创建新的列，并统计每个员工参与的项目数量。

为你提供更详细的说明。

在 Oracle 中，行转列操作通常使用 PIVOT 关键字来实现。

下面是一个更详细的示例，假设我们有一个名为 employee_projects 的表，包含了 employee_id 和 project_id 列，我们想要将 project_id 转换为列进行汇总统计。

首先，我们可以使用如下的查询来展示员工参与项目的情况：```sqlSELECT * FROM employee_projects;```接下来，我们可以使用 PIVOT 关键字将项目按照 employee_id 进行汇总统计，生成每个项目的统计数量：```sqlSELECT *FROM (SELECT employee_id, project_idFROM employee_projects)PIVOT (COUNT(project_id)FOR project_id IN (1 AS project_1, 2 AS project_2, 3 AS project_3));```在这个示例中，PIVOT 子句将会根据 project_id 的值创建新的列，并统计每个员工参与的项目数量。

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进行行列转换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⾏转列（不固定⾏数的⾏转列，动态）（转）1. Oracle 11g之后新增了⾏列转换的函数 pivot 和 unpivot⼤⼤简化了⾏列转换处理。

2. 在Oracle 10g及以前版本，⼀般是通过各种SQL进⾏⾏列转换，列⼊下⾯例⼦：create or replace procedure P_row_to_col(tabname in varchar2,group_col in varchar2,column_col in varchar2,value_col in varchar2,Aggregate_func in varchar2default'max',colorder in varchar2default null,roworder in varchar2default null,when_value_null in varchar2default null,viewname in varchar2default'v_tmp')Authid Current_User as sqlstr varchar2(2000) :='create or replace view '|| viewname ||' as select '|| group_col ||'';c1 sys_refcursor;v1 varchar2(100);beginopen c1 for'select distinct '|| column_col ||' from '|| tabname ||case when colorder isnot null then' order by '|| colorderend;loop fetch c1 into v1;exit when c1%notfound;sqlstr := sqlstr || chr(10) ||','||case when when_value_null isnot null then'nvl('end|| Aggregate_func ||'(decode(to_char('|| column_col ||'),'''|| v1 ||''','|| value_col ||'))'||case when when_value_null isnot null then chr(44) || when_value_null || chr(41)end||'"'|| v1 ||'"';end loop;close c1;sqlstr := sqlstr ||' from '|| tabname ||' group by '|| group_col ||case when roworder isnot null then' order by '|| roworderend;execute immediate sqlstr;end P_row_to_col;--测试数据create table rowtocol_test asselect2009year,1month,'AAA1' dept,50000 expenditure from dualunion all select2009,2,'AAA1',20000from dualunion all select2009,2,'AAA1',30000from dualunion all select2010,1,'AAA1',35000from dualunion all select2009,2,'BBB2',40000from dualunion all select2009,3,'BBB2',25000from dualunion all select2010,2,'DDD3',60000from dualunion all select2009,2,'DDD3',15000from dualunion all select2009,2,'DDD3',10000from dual;select*from rowtocol_test;--执⾏测试beginp_row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func =>'sum',colorder =>'dept',roworder =>'1,2',when_value_null =>'0');end;select*from v_tmp;================================================================================================================例⼦⼆三：--测试数据create table t (XH varchar2(10), DDATE date, SXF int);insert into t select1,sysdate,10from dualunion all select1,sysdate+1,14from dualunion allselect 1,sysdate+2,23from dualunion allselect 2,sysdate,21from dualunion allselect 2,sysdate+1,24from dualunion allselect 3,sysdate,13from dualunion allselect 3,sysdate+1,22from dual;--create or replace package sp_test istype ResultData is ref cursor;procedure getRstData( rst out ResultData); end sp_test;/create or replace package body sp_test isprocedure getRstData( rst out ResultData)is begindeclare cursor cur is select distinct (DDATE) from t;tmp_ddate date;str varchar2(4000);beginstr:='select xh';open cur;loop fetch cur into tmp_ddate;exit when cur%notfound;str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"'; end loop;str:=str||' from t group by xh';--dbms_output.put_line(str);close cur;open rst for str; end;end;end sp_test;/--输出结果1101423221240313220========================例⼦三：------------建表CREATE TABLE TEST(WL VARCHAR2(10),XYSL INTEGER,XYCK VARCHAR2(10),XCLCK VARCHAR2(10),XCLCKSL INTEGER,PC INTEGER); ------------ 第⼀部分测试数据INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333);INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223);COMMIT;-------------------- 动态⽣成结果表DECLARE V_SQL VARCHAR2(2000);CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;BEGIN V_SQL :='SELECT WL,XYSL,XYCK';FOR V_XCLCK IN CURSOR_1LOOP V_SQL := V_SQL ||','||'SUM(DECODE(XCLCK,'''|| V_XCLCK.XCLCK ||''',XCLCKSL,0)) AS '|| V_XCLCK.XCLCK;END LOOP;V_SQL := V_SQL ||' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';--DBMS_OUTPUT.PUT_LINE(V_SQL);V_SQL :='CREATE TABLE RESULT AS '|| V_SQL;--DBMS_OUTPUT.PUT_LINE(V_SQL);EXECUTE IMMEDIATE V_SQL;END;--------------- 结果SELECT*FROM RESULT T;。

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的：。

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个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。