oracle列转行sql详细讲解
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。
sql语句中行转列，以及列转行
sql语句中⾏转列，以及列转⾏⾏转列：图1： --------------------------------------------》》》》 图2：sql执⾏原理：根据id分组，然后select后⾯创建多次查询，⽣成列信息（利⽤case语句给分group by后的语句分类）-- ⾏转列select t.id,sum(case name when'仓库1'then t.num else NULL end) 仓库1,sum(case name when'仓库2'then t.num else NULL end) 仓库2,sum(case name when'仓库3'then t.num else NULL end) 仓库3from tGROUP BY t.id;列转⾏：图1： --------------------------------------------》》》》 图2：第⼀步：sql执⾏原理：每个select语句只查某⼀个字段的值，创建多个查询，然后将多个select语句通过union链接，实现⼀条多列数据变成多⾏⼀列；⽐如⼀⾏仓库1，仓库2，仓库3 最后变成多⾏select p.id, '仓库1' name, p.`仓库1` numfrom pr punionselect p.id, '仓库2' name, p.`仓库2` numfrom pr punionselect p.id, '仓库3' name, p.`仓库3` numfrom pr p执⾏结果：第⼆步：去掉为null的，即不存在⾏select*from (select p.id, '仓库1' name, p.`仓库1` num from pr punionselect p.id, '仓库2' name, p.`仓库2` num from pr punionselect p.id, '仓库3' name, p.`仓库3` num from pr p ) T where T.num is not null order by id, name。
oracle 列转行sql详解
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：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行列转换关系
最近论坛很多人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，希望对大家有所帮助，同时有何错疏，恳请大家指出，我也是在写作过程中学习，算是一起和大家学习吧。
行列转换包括以下六种情况：*列转行*行转列*多列转换成字符串*多行转换成字符串*字符串转换成多列*字符串转换成多行下面分别进行举例介绍。
首先声明一点，有些例子需要如下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语句总结多⾏转字符串这个⽐较简单，⽤||或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个字段。
sql查询结果列转行方法
sql查询结果列转行方法要将SQL查询结果的列转为行，我们一般可以使用UNIONALL操作符来实现。
下面是详细步骤：1.首先，我们需要确保查询结果的列类型和取值范围是一致的。
如果有需要，可以使用CAST(或CONVERT(函数进行类型转换。
2.使用UNIONALL操作符将所有列合并成一列。
每个SELECT语句表示一个原始列，其中的列名称可以通过别名进行重命名。
例如：SELECT column1 AS column FROM tableUNIONALLSELECT column2 AS column FROM tableUNIONALLSELECT column3 AS column FROM table这样我们就将table表中的column1、column2和column3列合并成一列，并使用别名column进行表示。
3.如果查询结果中有多个表，我们需要使用JOIN操作符将这些表连接起来，然后再进行列转行的操作。
例如：SELECT column1 AS column FROM table1JOIN table2 ON table1.id = table2.idUNIONALLSELECT column2 AS column FROM table1JOIN table2 ON table1.id = table2.id这里我们将table1和table2两个表连接起来，并将table1中的column1和table2中的column2列合并成一列。
4.如果查询结果中有多个条件或是函数，我们可以使用子查询来实现列转行。
例如：SELECT columnFROMSELECT col1 + col2 AS column FROM tableWHERE conditionUNIONALLSELECT col3 - col4 AS column FROM tableWHERE conditionAS subquer这里我们将table表中col1和col2两列相加，并将结果作为column列，然后与table表中col3和col4两列相减的结果进行合并。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
--当期时间贷款时间SELECT DK_ID, max(substr(activeDate, 2)) activeDateFROM (SELECT DK_ID, sys_connect_by_path(activeDate, ',') activeDateFROM (SELECT DK_ID,activeDate,DK_ID || rn rchild,DK_ID || (rn - 1) rfatherFROM (SELECT TEMP.DK_ID,--查询项目所在地树形结构全名SELECT t.area_id,substr(sys_connect_by_path(t.area_name, '-'), 2) as allname ,connect_by_root t.area_name as root, --是单一操作符，返回当前层的最顶层节点connect_by_isleaf as IsLeaf, --是伪列，判断当前层是否为叶子节点，1代表是，0代表否level as lel --是伪列，显示当前节点层所处的层数FROM dk_project_area_info tSTART WITH t.area_name = '项目所在地'CONNECT BY PRIOR t.area_id = t.area_pidSYS_CONNECT_BY_PATH 学习2008-09-08 10:59SELECT enameFROM scott.empSTART WITH ename = 'KING'CONNECT BY PRIOR empno = mgr;得到结果为：KINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMES而：SELECT SYS_CONNECT_BY_PATH(ename, '>') "Path"FROM scott.empSTART 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>KING>BLAKE>KING>BLAKE>ALLEN>KING>BLAKE>WARD>KING>BLAKE>MARTIN>KING>BLAKE>TURNER>KING>BLAKE>JAMES>KING>CLARK>KING>CLARK>MILLER其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！它一定要和connect by子句合用！第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！START WITH 代表你要开始遍历的的节点，CONNECT BY PRIOR 是标示父子关系的对应！如下例子：select max(substr(sys_connect_by_path(column_name,','),2))from (select column_name,rownum rn from user_tab_columns where table_name ='AA_TEST')start with rn=1 connect by rn=rownum ;是将列用,进行分割成为一行，然后将首个，去掉，只取取最大的那个数据。
－－－－－－－－－下面是别人的例子：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,rownum rn 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,MGRNOconnect by 例子2009-04-21 09:18层次查询子句connect by，用于构造层次结果集的查询。
语法：[ START WITH condition ]CONNECT BY [ NOCYCLE ] condition说明：a、START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。
b、当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。
此时，需要在connect by后面加上NOCYCLE关键字。
同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。
connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用，用法见示例2。
用法举例：示例1：显示所有地名关系结构。
SQL> select * from t;AREA_ID AREA_NAME MGR_ID-------- ---------- ------86 中国01 8602 860101 海淀区 010102 区 010103 东城区 010104 西城区 010201 020202 02020101 湖里 0201020102 思明 0201010401 复兴门 0104010402 西单 0104已选择13行。
SQL>SQL> set pagesize 50SQL> col AreaName for a12SQL> col Root for a10SQL> col Path for a24SQL>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"6 from t7 start with mgr_id is null8 connect by prior area_id = mgr_id;AreaName Root IsLeaf LEVEL Path------------ ---------- ------ ---------- ------------------------中国中国 0 1 /中国中国 0 2 /中国/海淀区中国 1 3 /中国//海淀区区中国 1 3 /中国//区东城区中国 1 3 /中国//东城区西城区中国 0 3 /中国//西城区复兴门中国 1 4 /中国//西城区/复兴门西单中国 1 4 /中国//西城区/西单中国 0 2 /中国/中国 0 3 /中国//湖里中国 1 4 /中国///湖里思明中国 1 4 /中国///思明中国 1 3 /中国//已选择13行。
说明：a、prior：是单一操作符，放在列名的前面，等号左右均可；放在父 ID 就是寻找祖先节点，放到本身 ID就是寻找子节点；b、connect_by_root：是单一操作符，返回当前层的最顶层节点；c、connect_by_isleaf：是伪列，判断当前层是否为叶子节点，1代表是，0代表否；d、level：是伪列，显示当前节点层所处的层数；e、SYS_CONNECT_BY_PATH：是函数，显示当前层的详细路径。
示例2：找出人事部门中存在跟其他部门互为管理者的人员。
SQL> select * from t2;EMP DEPT MGR------------ ------ ----------涛总裁办飞总裁办涛强总裁办涛王鹏人事飞华人事飞强人事飞飞行政强吴华行政强已选择8行。
SQL>SQL> col emp for a12SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"2 from t23 start with dept ='人事'4 connect by prior emp = mgr;ERROR:ORA-01436: 用户数据中的 CONNECT BY 循环未选定行说明：强和飞互为管理者，因此，要用nocycle，如下所示：SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"2 from t23 start with dept ='人事'4 connect by nocycle prior emp = mgr;emp------------王鹏华强飞王鹏华吴华已选择7行。