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 列转行sql详解

2 from t2
3 start with dept ='人事'
4 connect by prior emp = mgr;
ERROR:
ORA-01436:用户数据中的CONNECT BY循环
未选定行
说明：张强和李飞互为管理者，因此，要用nocycle，如下所示：
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"
SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || area_name "AreaName",
2 connect_by_root area_name "Root",
3 connect_by_isleaf "IsLeaf",
4 level ,
5 SYS_CONNECT_BY_PATH(area_name, '/') "Path"
START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr;
得到结果为：>KING
>KING>JONES
>KING>JONES>SCOTT
>KING>JONES>SCOTT>ADAMS
>KING>JONES>FORD
>KING>JONES>FORD>SMITH
connect_by_root t.area_name as root, --是单一操作符，返回当前层的最顶层节点

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

oraclesql一列转多行最简单的方法在Oracle SQL中，一列转换为多行的最简单方法是使用UNION ALL 操作符结合SELECT语句。

下面给出一个例子来说明该方法。

假设我们有一个包含员工姓名和城市的表格，我们想将这些信息分开显示在不同的行上。

首先，我们创建一个示例表格并插入一些数据。

CREATE TABLE employeesemployee_id NUMBER,employee_name VARCHAR2(100),city VARCHAR2(100)INSERT INTO employees VALUES (1, 'John Doe', 'New York');INSERT INTO employees VALUES (2, 'Jane Smith', 'Los Angeles');INSERT INTO employees VALUES (3, 'Mike Johnson', 'Chicago');现在，我们可以使用UNIONALL操作符来将姓名和城市分开显示在不同的行上。

SELECT employee_name AS data FROM employeesUNIONALLSELECT city AS data FROM employees;在这个例子中，我们先选择员工姓名并将其命名为"data"，然后使用UNION ALL操作符将结果与选择城市结果进行合并。

最终的结果是将一列数据转换为多行数据。

结果如下：DATA---------John DoeJane SmithMike JohnsonNew YorkLos AngelesChicago这种方法非常简单直观，但需要注意的是，UNIONALL操作符合并结果时会保留重复的行。

如果你希望去除重复的值，可以使用UNION操作符代替UNIONALL。

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行转列、列转行的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多行变一行今天遇到将多行转为一行的一个操作，多谢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.自定义函数另一个方法是自定义一个函数解决问题。

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

oracle列转行
1------------------------
表结构：
1A
1B
1C
2A
2B
3C
3F
4D
转换后变成：
1A,B,C
2A,B
3C,F
4D
假设你的表结构是tb_name(id,remark),则语句如下:
SELECT
a.id,
wm_concat(a.remark)new_result
FROM
tb_name a
group by
a.id
2----------------------------------
产品名称销售额季度
奶酪50第一季度
奶酪60第二季度
啤酒50第二季度
啤酒80第四季度。

想转换成如下格式
产品名称第一季度销售额第二季度销售额第三季度销售额第四季度销售额
奶酪50600 0
啤酒0500
80
oracle下可以用函数decode处理:
select产品名称,
sum(decode(季度,'第一季度',销售额,0))第一季度销售额,
sum(decode(季度,'第二季度',销售额,0))第二季度销售额,
sum(decode(季度,'第三季度',销售额,0))第三季度销售额,
sum(decode(季度,'第四季度',销售额,0))第四季度销售额,
from表名
group by产品名称;
3-------------------------------------------
oracle行转列的通用过程2010-04-0923:28经常遇到发帖求行列转换的代码，用max(decode(..))回复后，十有八九会再问一句：如果列名不固定，或者列数不固定怎么办。

就要用存储过程来写，这些存储过程的代码都大同小异，我就想能不能写个通用点的过程
试了一下，把结果发出来
SQL code
create or replace procedure proc(tabname in varchar2,
col1in varchar2,
col2in varchar2,
col3in varchar2,
viewname in varchar2default'v_tmp')
as
sqlstr varchar2(2000):='create or replace view '||viewname||'as select'||col1||''; c1sys_refcursor;
v1varchar2(100);
begin
open c1for'select distinct to_char('||col2||') from'||tabname;
loop
fetch c1into v1;
exit when c1%notfound;
sqlstr:=sqlstr||'
,max(decode('||col2||','''||v1||' '','||col3||'))"'||v1||'&quo
t;';
end loop;
close c1;
sqlstr:=sqlstr||'from'||tabname||'group by '||col1;
execute immediate sqlstr;
end proc;
这里的几个参数，tabname指的是需要进行行列转换的表名，col1是这个表中行列转换以后要根据哪一列进行分组，那一列的列名。

col2传入的是要将行转成列的那一列的列名，col3表示需要进行统计的数据列的列名
viewname传入希望建立的视图的名称，可以不填，默认为v_tmp 这么说很难让人明白..举个例子，引用一个帖子的数据
create table tab(
counter varchar(20),--参加考试人数
subject varchar(20),--科目
class varchar(20)--
班级)
表数据：
counter subject class
36
英语一班
44英语二班44数学二班33语文一班39语文三班
转换后：
一班二班三班
英语36440
数学0440
语文33039
编译好过程后，执行
SQL code
begin
proc('tab','subject','class',& #39;counter');
end;
--结果
select*from v_tmp;
SUBJECT一班三班二班
数学44
英语3644
语文3339
如果对这个结果不是很满意，需要自己进行一些修改，比如空值的地方用0代替，或者需要用别的函数聚合而不是max。

可以将过程中的execute immediate那句改成
dbms_output.put_line(sqlstr);
重新编译，执行，输出代码
如果用的是pl/sql dev的sql窗口，到output窗口查看
SQL code
--看到生成的代码
create or replace view v_tmp as select subject
,max(decode(class,'一班',counter))"一班"
,max(decode(class,'三班',counter))"三班"
,max(decode(class,'二班',counter))"二班"from tab group by subject
再加入nvl()，达到修改的目的
如果不想创建这样一个过程，则改成匿名块，需要时运行
SQL code
declare
tabname varchar2(20):='XXX';--'XXX'分别用相应的表名和字段名代替
col1varchar2(10):='XXX';
col2varchar2(10):='XXX';
col3varchar2(10):='XXX';
viewname in varchar2(10):='v_tmp';
sqlstr varchar2(2000):='create or replace view '||viewname||'as select'||col1||''; c1sys_refcursor;
v1varchar2(100);
begin
open c1for'select distinct to_char('||col2||') from'||tabname;
loop
fetch c1into v1;
exit when c1%notfound;
sqlstr:=sqlstr||'
,max(decode('||col2||','''||v1||' '','||col3||'))"'||v1||'&quo t;';
end loop;
close c1;
sqlstr:=sqlstr||'from'||tabname||'group by '||col1;
--execute immediate sqlstr; dbms_output.put_line(sqlstr); end;
4-----------------------------------------------。