oracle 列转行sql详解

--当期时间贷款时间SELECTDK_ID,max(substr(activeDate,2))activeDateFROM(SELECTDK_ID,sys_connect_by_path(activeDate,',')activeDateFROM(SELECTDK_ID,activeDate,DK_ID||rnrchild,DK_ID||(rn-1)rfatherFROM(SELECTTEMP.DK_ID,--查询项目所在地树形结构全名SELECTt.area_id,substr(sys_connect_by_path(t.area_name,'-'),2)asallname,connect_by_roott.area_nameasroot,--是单一操作符，返回当前层的最顶层节点connect_by_isleafasIsLeaf,--是伪列，判断当前层是否为叶子节点，1代表是，0代表否levelaslel--是伪列，显示当前节点层所处的层数FROMdk_project_area_infotSTARTWITHt.area_name='项目所在地'CONNECTBYPRIORt.area_id=t.area_pidSYS_CONNECT_BY_PATH学习2008-09-0810:59SELECTenameFROMscott.empSTARTWITHename='KING'CONNECTBYPRIORempno=mgr;得到结果为：KINGJONESSCOTTADAMSFORDSMITHBLAKEALLENWARDMARTINTURNERJAMES而：SELECTSYS_CONNECT_BY_PATH(ename,'')PathFROMscott.empSTARTWITHename='KING'CONNECTBYPRIORempno=mgr;得到结果为：KINGKINGJONESKINGJONESSCOTTKINGJONESSCOTTADAMSKINGJONESFORDKINGJONESFORDSMITHKINGBLAKEKINGBLAKEALLENKINGBLAKEWARDKINGBLAKEMARTINKINGBLAKETURNERKINGBLAKEJAMESKINGCLARKKINGCLARKMILLER其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的！它一定要和connectby子句合用！第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！STARTWITH代表你要开始遍历的的节点，CONNECTBYPRIOR是标示父子关系的对应！如下例子：selectmax(substr(sys_connect_by_path(column_name,','),2))from(selectcolumn_name,rownumrnfromuser_tab_columnswheretable_name='AA_TEST')startwithrn=1connectbyrn=rownum;是将列用,进行分割成为一行，然后将首个，去掉，只取取最大的那个数据。－－－－－－－－－下面是别人的例子：1、带层次关系SQLcreatetabledept(deptnonumber,deptnamevarchar2(20),mgrnonumber);Tablecreated.SQLinsertintodeptvalues(1,'总公司',null);1rowcreated.SQLinsertintodeptvalues(2,'浙江分公司',1);1rowcreated.SQLinsertintodeptvalues(3,'杭州分公司',2);1rowcreated.SQLcommit;Commitcomplete.SQLselectmax(substr(sys_connect_by_path(deptname,','),2))fromdeptconnectbypriordeptno=mgrno;MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))--------------------------------------------------------------------------------总公司,浙江分公司,杭州分公司2、行列转换如把一个表的所有列连成一行，用逗号分隔:SQLselectmax(substr(sys_connect_by_path(column_name,','),2))from(selectcolumn_name,rownumrnfromuser_tab_columnswheretable_name='DEPT')startwithrn=1connectbyrn=rownum;MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))--------------------------------------------------------------------------------DEPTNO,DEPTNAME,MGRNOconnectby例子2009-04-2109:18层次查询子句connectby，用于构造层次结果集的查询。语法：[STARTWITHcondition]CONNECTBY[NOCYCLE]condition说明：a、STARTWITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。b、当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connectby后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用，用法见示例2。用法举例：示例1：显示所有地名关系结构。SQLselect*fromt;AREA_IDAREA_NAMEMGR_ID------------------------86中国01北京8602福建860101海淀区010102朝阳区010103东城区010104西城区010201厦门020202福州02020101湖里0201020102思明0201010401复兴门0104010402西单0104已选择13行。SQLSQLsetpagesize50SQLcolAreaNamefora12SQLcolRootfora10SQLcolPathfora24SQLSQLselectrpad('',2*(level-1),'')||area_nameAreaName,2connect_by_rootarea_nameRoot,3connect_by_isleafIsLeaf,4level,5SYS_CONNECT_BY_PATH(area_name,'/')Path6fromt7startwithmgr_idisnull8connectbypriorarea_id=mgr_id;AreaNameRootIsLeafLEVELPath--------------------------------------------------------------中国中国01/中国北京中国02/中国/北京海淀区中国13/中国/北京/海淀区朝阳区中国13/中国/北京/朝阳区东城区中国13/中国/北京/东城区西城区中国03/中国/北京/西城区复兴门中国14/中国/北京/西城区/复兴门西单中国14/中国/北京/西城区/西单福建中国02/中国/福建厦门中国03/中国/福建/厦门湖里中国14/中国/福建/厦门/湖里思明中国14/中国/福建/厦门/思明福州中国13/中国/福建/福州已选择13行。说明：a、prior：是单一操作符，放在列名的前面，等号左右均可；放在父ID就是寻找祖先节点，放到本身ID就是寻找子节点；b、connect_by_root：是单一操作符，返回当前层的最顶层节点；c、connect_by_isleaf：是伪列，判断当前层是否为叶子节点，1代表是，0代表否；d、level：是伪列，显示当前节点层所处的层数；e、SYS_CONNECT_BY_PATH：是函数，显示当前层的详细路径。示例2：找出人事部门中存在跟其他部门互为管理者的人员名单。SQLselect*fromt2;EMPDEPTMGR----------------------------刘涛总裁办李飞总裁办刘涛张强总裁办刘涛王鹏人事李飞李华人事李飞张强人事李飞李飞行政张强吴华行政张强已选择8行。SQLSQLcolempfora12SQLselectrpad('',2*(level-1),'')||empemp2fromt23startwithdept='人事'4connectbyprioremp=mgr;ERROR:ORA-01436:用户数据中的CONNECTBY循环未选定行说明：张强和李飞互为管理者，因此，要用nocycle，如下所示：SQLselectrpad('',2*(level-1),'')||empemp2fromt23startwithdept='人事'4connectbynocycleprioremp=mgr;emp------------王鹏李华张强李飞王鹏李华吴华已选择7行。SQLSQLselectrpad('',2*(level-1),'')||empemp,2connect_by_iscycleIsCycle3fromt24startwithdept='人事'5connectbyprioremp=mgr;connectbyprioremp=mgr*第5行出现错误:ORA-30930:CONNECT_BY_ISCYCLE伪列要求NOCYCLE关键字说明：在用connect_by_iscycle定位节点时，也要用nocycle关键字，如下所示：SQLSQLselectrpad('',2*(level-1),'')||empemp,2connect_by_iscycleIsCycle3fromt24startwithdept='人事'5connectbynocycleprioremp=mgr;empIsCycle-------------------王鹏0李华0张强0李飞1王鹏0李华0吴华0已选择7行。SQL示例3：仅显示第二层（即level=2）省市名称。SQLselectrpad('',2*(level-1),'')||area_nameAreaName2fromt3wherelevel=24startwithmgr_idisnull5connectbypriorarea_id=mgr_id;AreaName------------北京福建示例4：用connectby构造序列。SQLSQLselectrownumrn2fromdual3connectbyrownum=10;RN----------12345678910已选择10行。SQLselectrownum*2-1rn2fromdual3connectbyrownum=10;RN----------135791113151719已选择10行。-----------------------------------------附：建表语句createtablet(area_idvarchar2(6),area_namevarchar2(10),mgr_idvarchar2(6));insertintotvalues('86','中国',null);insertintotvalues('01','北京','86');insertintotvalues('02','