多行转字符串
这个比较简单,用||或concat函数可以实现
[sql]
selectconcat(id,username)strfromapp_user
selectid||usernamestrfromapp_user
字符串转多列
实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式
字符串转多行
使用union all函数等方式
wm_concat函数
首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据
[sql]
createtabletest(idnumber,namevarchar2(20));
insertintotestvalues(1,"a");
insertintotestvalues(1,"b");
insertintotestvalues(1,"c");
insertintotestvalues(2,"d");
insertintotestvalues(2,"e");
效果1 : 行转列 ,默认逗号隔开
[sql]
selectwm_concat(name)namefromtest;
效果2: 把结果里的逗号替换成"|"
[sql]
selectreplace(wm_concat(name),",","|")fromtest;
效果3: 按ID分组合并name
[sql]
selectid,wm_concat(name)namefromtestgroupbyid;
sql语句等同于下面的sql语句
[sql]
--------适用范围: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(partitionbyidorderbyname)asrnfromtest)tgroupbyidorderby1;
--------适用范围:8i,9i,10g及以后版本(ROW_NUMBER+LEAD)
selectid,strfrom(selectid,row_number()over(partitionbyidorderbyname)asrn,name||lead(","||name,1)
over(partitionbyidorderbyname)||lead(","||name,2)over(partitionbyidorderbyname)||lead(","||name,3)
over(partitionbyidorderbyname)asstrfromtest)wherern=1orderby1;
--------适用范围:10g及以后版本(MODEL)
selectid,substr(str,2)strfromtestmodelreturnupdatedrowspartitionby(id)dimensionby(row_number()
over(partitionbyidorderbyname)asrn)measures(cast(nameasvarchar2(20))asstr)rulesupsertiterate(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.idid,max(substr(sys_connect_by_path(t.name,","),2))strfrom(selectid,name,row_number()
over(partitionbyidorderbyname)rnfromtest)tstartwithrn=1connectbyrn=priorrn+1andid=priorid
groupbyt.id;
懒人扩展用法:
案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单,假设我的APP_USER表中有(id,username,password,age)4个字段。查询结果如下
[sql]
/**这里的表名默认区分大小写*/
select"createorreplaceviewasselect"||wm_concat(column_name)||"fromAPP_USER"sqlStr
fromuser_tab_columnswheretable_name="APP_USER";
利用系统表方式查询
[sql]
select*fromuser_tab_columns
Oracle 11g 行列互换 pivot 和 unpivot 说明
在Oracle 11g中,oracle 又增加了2个查询:pivot(行转列)和unpivot(列转行)
参考:/tianlesoftware/article/details/7060306、/technetwork/cn/articles/11g-pivot-101924-zhs.html
google 一下,网上有一篇比较详细的文档:http://www.oracle-/display.php?id=506
pivot 列转行
测试数据 (id,类型名称,销售数量),案例:根据水果的类型查询出一条数据显示出每种类型的销售数量。
[sql]
createtabledemo(idint,namevarchar(20),numsint);----创建表
insertintodemovalues(1,"苹果",1000);
insertintodemovalues(2,"苹果",2000);
insertintodemovalues(3,"苹果",4000);
insertintodemovalues(4,"橘子",5000);
insertintodemovalues(5,"橘子",3000);
insertintodemovalues(6,"葡萄",3500);
insertintodemovalues(7,"芒果",4200);
insertintodemovalues(8,"芒果",5500);
分组查询 (当然这是不符合查询一条数据的要求的)
[sql]
selectname,sum(nums)numsfromdemogroupbyname
行转列查询
[sql]
select*from(selectname,numsfromdemo)pivot(sum(nums)fornamein("苹果"苹果,"橘子","葡萄","芒果"));
注意: pivot(聚合函数 for 列名 in(类型)) ,其中 in(‘’) 中可以指定别名,in中还可以指定子查询,比如 select distinct code from customers
当然也可以不使用pivot函数,等同于下列语句,只是代码比较长,容易理解
[sql]
------多项子查询
select*from(selectsum(nums)苹果fromdemowherename="苹果"),(selectsum(nums)橘子fromdemowherename="橘子"),
(selectsum(nums)葡萄fromdemowherename="葡萄"),(selectsum(nums)芒果fromdemowherename="芒果");
------decode函数利用
selectsum(decode(name,"苹果",nums))苹果,sum(decode(name,"橘子",nums))橘子,
sum(decode(name,"葡萄",nums))葡萄,sum(decode(name,"芒果",nums))芒果fromdemo
unpivot 行转列
顾名思义就是将多列转换成1列中去
案例:现在有一个水果表,记录了4个季度的销售数量,现在要将每种水果的每个季度的销售情况用多行数据展示。
创建表和数据
[sql]
createtableFruit(idint,namevarchar(20),Q1int,Q2int,Q3int,Q4int);
insertintoFruitvalues(1,"苹果",1000,2000,3300,5000);
insertintoFruitvalues(2,"橘子",3000,3000,3200,1500);
insertintoFruitvalues(3,"香蕉",2500,3500,2200,2500);
insertintoFruitvalues(4,"葡萄",1500,2500,1200,3500);
select*fromFruit
列转行查询
[sql]
selectid,name,jidu,xiaoshoufromFruitunpivot(xiaoshouforjiduin(q1,q2,q3,q4))
注意: unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量
同样不使用unpivot也可以实现同样的效果,只是sql语句会很长,而且执行速度效率也没有前者高
[sql]
selectid,name,"Q1"jidu,(selectq1fromfruitwhereid=f.id)xiaoshoufromFruitf
union
selectid,name,"Q2"jidu,(selectq2fromfruitwhereid=f.id)xiaoshoufromFruitf
union
selectid,name,"Q3"jidu,(selectq3fromfruitwhereid=f.id)xiaoshoufromFruitf
union
selectid,name,"Q4"jidu,(selectq4fromfruitwhereid=f.id)xiaoshoufromFruitf
。。。。
此处省略部分内容,完整内容请查看原文:/article/260.html