ORACLE行转列简易学习文档.docVIP

ORACLE行转列入门 ――刘明智 一、初始化数据 --DROP TABLE student; CREATE TABLE student( no number, name varchar2(12), course varchar2(40) ); INSERT INTO student VALUES(001,刘明智,计算机组成原理); INSERT INTO student VALUES(001,刘明智,软件工程); INSERT INTO student VALUES(001,刘明智,离散数学); INSERT INTO student VALUES(002,赵燕,JAVA程序设计); INSERT INTO student VALUES(002,赵燕,ASP.NET程序设计); INSERT INTO student VALUES(003,王海金,ORACLE数据库); INSERT INTO student VALUES(003,王海金,SSH框架); INSERT INTO student VALUES(003,王海金,AJAX设计); COMMIT; SELECT * FROM student; 效果如下： --先了解ORACLE几个函数 LTRIM(str1,str2);--截去字符串函数，字符串str1从头部截去str2 ROW_NUMBER()OVER(ORDER BY colnum)--给查出来的记录加上编号，并按colnum排序 LAG(rn)OVER(PARTITION BY no ORDER BY rn) nextrn -- 将表按no分组，升序排序，把本行的rn字段的值赋给下一行的nextrn字段（构造正树） LEAD()OVER()--与LAG()OVER()相反，是把下一行的rn字段赋给本行的nextrn字段（即构造倒树） sys_connect_by_path(course, ;)--把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示 二、行转列详解 核心：把表里面的数据构造成一棵一棵的树一型结构 步聚一：为表的每行记录加一个编号 SELECT s.no,s.name,s.course, ROW_NUMBER()OVER(ORDER BY s.no , s.course desc) rn FROM student s 效果如下： 步骤二：将表按no分组，升序排序，把本行的rn字段的值赋给下一行的nextrn字段 SELECT no,name,course,rn,LAG(rn)OVER(PARTITION BY no ORDER BY rn) nextrn FROM ( SELECT s.no,s.name,s.course, ROW_NUMBER()OVER(ORDER BY s.no , s.course desc) rn FROM student s) s2 步骤三：从nextrn为null的那条记录开始，递归查找，如果一记录的nextrn字段等于当前记录的rn字段，就把2条记录的Value用分号连接起来 SELECT no AS , name AS 姓名, sys_connect_by_path(course, ;) AS 选课 FROM( SELECT no,name,course,LAG(rn)OVER(PARTITION BY no ORDER BY rn) nextrn,rn FROM ( SELECT s.no,s.name,s.course, ROW_NUMBER()OVER(ORDER BY s.no , s.course desc) rn FROM student s) s2) s3 START WITH nextrn IS NULL CONNECT BY prior rn =nextrn 效果如下： 步骤三：分组，格式一下，把选课字段前的分号去掉，得到最终结果。 sys_connect_by_path(course, ;)函数是关键 SELECT no AS 编号, name AS 姓名, LTRIM(max(sys_connect_by_path(course, ;)), ;) AS 选课 FROM( SELECT no,name,course,rn,LAG(rn)OVER(PARTITION BY no ORDER BY rn) nextrn FROM ( SEL