Oracle行转列、列转行的Sql语句总结

多行转字符串
这个比较简单，用||或concat函数可以实现
SQL Code

selectconcat(id,username)strfromapp_user
selectid||usernamestrfromapp_user

字符串转多列
实际上就是拆分字符串的问题，可以使用 substr、instr、regexp_substr函数方式
字符串转多行
使用union all函数等方式
wm_concat函数
首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据
SQL Code

createtabletest(idnumber,namevarchar2(20));
insertintotestvalues(1,'a');
insertintotestvalues(1,'b');
insertintotestvalues(1,'c');
insertintotestvalues(2,'d');
insertintotestvalues(2,'e');

效果1 : 行转列 ，默认逗号隔开
SQL Code

selectwm_concat(name)namefromtest;

效果2: 把结果里的逗号替换成"|"
SQL Code

selectreplace(wm_concat(name),',','|')fromtest;

效果3: 按ID分组合并name
SQL Code

selectid,wm_concat(name)namefromtestgroupbyid;

sql语句等同于下面的sql语句:
SQL Code

--------适用范围：8i,9i,10g及以后版本（MAX+DECODE）

selectid,max(decode(rn,1,name,null))||max(decode(rn,2,','||name,null))||max(decode(rn,3,','||name,null))strfrom(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=prioridgroupbyt.id;

懒人扩展用法:
案例:
我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单，假设我的APP_USER表中有（id,username,password,age）4个字段。查询结果如下
SQL Code
/*这里的表名默认区分大小写/

select'createorreplaceviewasselect'||wm_concat(column_name)||'fromAPP_USER'sqlStr
fromuser_tab_columns
wheretable_name='APP_USER';

利用系统表方式查询
SQL Code

select*fromuser_tab_columns

Oracle 11g 行列互换 pivot 和 unpivot 说明
在Oracle 11g中，Oracle 又增加了2个查询：pivot（行转列） 和unpivot（列转行）
参考：http://blog.csdn.net/tianlesoftware/article/details/7060306、http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
google 一下，网上有一篇比较详细的文档：http://www.oracle-developer.net/display.php?id=506
pivot 列转行
测试数据 (id，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。
SQL Code

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 Code

selectname,sum(nums)numsfromdemogroupbyname

行转列查询
SQL Code

select*from(selectname,numsfromdemo)pivot(sum(nums)fornamein('苹果'苹果,'橘子','葡萄','芒果'));

注意： pivot（聚合函数 for 列名 in（类型）） ，其中 in('') 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers
当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解
SQL Code

select*from(selectsum(nums)苹果
fromdemowherename='苹果'),
(selectsum(nums)橘子fromdemowherename='橘子'),
(selectsum(nums)葡萄fromdemowherename='葡萄'),
(selectsum(nums)芒果fromdemowherename='芒果');

unpivot 行转列
顾名思义就是将多列转换成1列中去案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。
创建表和数据
SQL Code

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 Code

selectid,name,jidu,xiaoshoufromFruitunpivot(xiaoshouforjiduin(q1,q2,q3,q4))

注意： unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量

同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高
SQL Code

selectid,name,'Q1'jidu,(selectq1fromfruitwhereid=f.id)xiaoshou
fromFruitfunionselectid,name,'Q2'jidu,
(selectq2fromfruitwhereid=f.id)xiaoshou
fromFruitfunionselectid,name,'Q3'jidu,
(selectq3fromfruitwhereid=f.id)xiaoshou
fromFruitfunionselectid,name,'Q4'jidu,
(selectq4fromfruitwhereid=f.id)xiaoshou
fromFruitf

XML类型
上述pivot列转行示例中，你已经知道了需要查询的类型有哪些，用in()的方式包含，假设如果您不知道都有哪些值，您怎么构建查询呢？
pivot操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出，在此输出中，您可以指定一个特殊的子句 ANY 而非文字值
示例如下：
SQL Code

select*from(selectname,numsas"PurchaseFrequency"fromdemot)pivotxml(sum(nums)fornamein(any))

如您所见，列 NAME_XML 是 XMLTYPE，其中根元素是 <PivotSet>。每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。

对于该xml文件的解析，贴代码如下：
SQL Code

createorreplaceprocedureljz_pivot_xml_sp(pi_table_namevarchar2,pi_column_namevarchar2,pi_create_tablevarchar2)
asv_columnnvarchar2(50);
v_countnumber:=0;
v_inumber;
v_parent_nodenvarchar2(4000);
v_child_nodenvarchar2(4000);
v_overboolean:=false;
v_tmpnvarchar2(50);
v_existsnodenumber;
v_sqlclob;
v_namevarchar2(30);
v_name_xmlxmltype;beginv_sql:='selectx.*from'||pi_table_name||'a,xmltable(''/PivotSet''passinga.'||pi_column_name||'columns';
v_parent_node:='/PivotSet';
v_child_node:='item[1]/column[2]';
v_i:=1;executeimmediate'select'||pi_column_name||'from'||pi_table_name||'whererownum=1'intov_name_xml;selectexistsnode(v_name_xml,'/PivotSet/item['||to_char(v_i)||']/column[1]')intov_existsnodefromdual;
whilev_existsnode=1loopexecuteimmediate'selectsubstr(extractvalue('||pi_column_name||',''/PivotSet/item['||to_char(v_i)||']/column[1]''),1,30)from'||pi_table_name||'x'intov_name;
v_sql:=v_sql||'"'||v_name||'"varchar2(30)path''item['||to_char(v_i)||']/column[2]'',';
v_i:=v_i+1;selectexistsnode(v_name_xml,'/PivotSet/item['||to_char(v_i)||']/column[1]')intov_existsnodefromdual;
endloop;
v_sql:=trim(','fromv_sql)||')x';

commit;
selectcount(1)intov_countfromuser_tab_columnswheretable_name=upper(pi_create_table);
ifv_count=0then
executeimmediate'createtable'||pi_create_table||'as'||v_sql;
endif;
end;

第一个参数为要解析xml文件所属数据表，第二个参数为要解析xml所存字段，第三个参数存放解析后的数据集。
测试：

begin
ljz_pivot_xml_sp('(select * from (select deptno,sal from emp) pivot xml(sum(sal) for deptno in(any)))',
'deptno_xml',
'ljz_pivot_tmp');
end;

初学oracle xml解析，这种方法较为笨拙，一个一个循环列，原型如下：

selectextractvalue(name_xml,'/PivotSet/item[1]/column[1]')
from(select*from(selectname,numsfromdemo)pivotxml(sum(nums)fornamein(any)))x
whereexistsnode(name_xml,'/PivotSet/item[1]/column[1]')=1;

selectx.*
from(select*
from(selectname,numsfromdemo)
pivotxml(sum(nums)
fornamein(any)))a,
xmltable('/PivotSet'passinga.name_xmlcolumns
芒果varchar2(30)path'item[1]/column[2]',
苹果varchar2(30)path'item[2]/column[2]')x

不知是否存在直接进行解析的方法，这种方法还不如直接行列转变，不通过xml转来转去。

select''''||listagg(substr(name,1,30),q'{','}')withingroup(orderbyname)||''''
from(selectdistinctnamefromdemo);

这样拼接字符串反而更加方便。

结论

Pivot为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表，而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样，您可以使用unpivot操作转换任何交叉表报表，以常规关系表的形式对其进行存储。Pivot可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出，您不必指定 pivot 操作需要搜索的值域。