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

朱雀 2022-11-06 12:52 279阅读 0赞

## 多行转字符串 ##

这个比较简单，用||或concat函数可以实现

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;concat(id,username)&nbsp;str&nbsp;from&nbsp;app_user select&nbsp;id||username&nbsp;str&nbsp;from&nbsp;app_user </td> 
 </tr> 
 </tbody> 
</table>

## 字符串转多列 ##

实际上就是拆分字符串的问题，可以使用 substr、instr、regexp\_substr函数方式

## 字符串转多行 ##

使用union all函数等方式

## wm\_concat函数 ##

首先让我们来看看这个神奇的函数**wm\_concat**(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 6 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> create&nbsp;table&nbsp;test(id&nbsp;number,name&nbsp;varchar2(20)); insert&nbsp;into&nbsp;test&nbsp;values(1,'a'); insert&nbsp;into&nbsp;test&nbsp;values(1,'b'); insert&nbsp;into&nbsp;test&nbsp;values(1,'c'); insert&nbsp;into&nbsp;test&nbsp;values(2,'d'); insert&nbsp;into&nbsp;test&nbsp;values(2,'e'); </td> 
 </tr> 
 </tbody> 
</table>

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

SQL Code

![5461cbf16e1c94b1ee7b04cb8cbac32e.png][]

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

SQL Code

![bcd9528f81477fd1ac40700025b9256a.png][]

### 效果3: 按ID分组合并name ###

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;id,wm_concat(name)&nbsp;name&nbsp;from&nbsp;test&nbsp;group&nbsp;by&nbsp;id; </td> 
 </tr> 
 </tbody> 
</table>

![1d880db3d413668622fc668d96fd15e3.png][]

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

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> --------&nbsp;适用范围：8i,9i,10g及以后版本&nbsp;&nbsp;（&nbsp;MAX&nbsp;+&nbsp;DECODE&nbsp;） select&nbsp;id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;max(decode(rn,&nbsp;1,&nbsp;name,&nbsp;null))&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;max(decode(rn,&nbsp;2,&nbsp;','&nbsp;||&nbsp;name,&nbsp;null))&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;max(decode(rn,&nbsp;3,&nbsp;','&nbsp;||&nbsp;name,&nbsp;null))&nbsp;str &nbsp;&nbsp;from&nbsp;(select&nbsp;id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;name, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;row_number()&nbsp;over(partition&nbsp;by&nbsp;id&nbsp;order&nbsp;by&nbsp;name)&nbsp;as&nbsp;rn &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;test)&nbsp;t &nbsp;group&nbsp;by&nbsp;id &nbsp;order&nbsp;by&nbsp;1; --------&nbsp;适用范围：8i,9i,10g及以后版本&nbsp;（&nbsp;ROW_NUMBER&nbsp;+&nbsp;LEAD&nbsp;） select&nbsp;id,&nbsp;str &nbsp;&nbsp;from&nbsp;(select&nbsp;id, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;row_number()&nbsp;over(partition&nbsp;by&nbsp;id&nbsp;order&nbsp;by&nbsp;name)&nbsp;as&nbsp;rn, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;name&nbsp;||&nbsp;lead(','&nbsp;||&nbsp;name,&nbsp;1)&nbsp;over(partition&nbsp;by&nbsp;id&nbsp;order&nbsp;by&nbsp;name)&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lead(','&nbsp;||&nbsp;name,&nbsp;2)&nbsp;over(partition&nbsp;by&nbsp;id&nbsp;order&nbsp;by&nbsp;name)&nbsp;||&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lead(','&nbsp;||&nbsp;name,&nbsp;3)&nbsp;over(partition&nbsp;by&nbsp;id&nbsp;order&nbsp;by&nbsp;name)&nbsp;as&nbsp;str &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;test) &nbsp;where&nbsp;rn&nbsp;=&nbsp;1 &nbsp;order&nbsp;by&nbsp;1; --------&nbsp;适用范围：10g及以后版本&nbsp;（&nbsp;MODEL&nbsp;） select&nbsp;id,&nbsp;substr(str,&nbsp;2)&nbsp;str &nbsp;&nbsp;from&nbsp;test&nbsp;model&nbsp;return&nbsp;updated&nbsp;rows&nbsp;partition&nbsp;by(id)&nbsp;dimension&nbsp;by(row_number()&nbsp; &nbsp;&nbsp;over(partition&nbsp;by&nbsp;id&nbsp;order&nbsp;by&nbsp;name)&nbsp;as&nbsp;rn)&nbsp;measures(cast(name&nbsp;as&nbsp;varchar2(20))&nbsp;as&nbsp;str)&nbsp; &nbsp;&nbsp;rules&nbsp;upsert&nbsp;iterate(3)&nbsp;until(presentv(str&nbsp;[&nbsp;iteration_number&nbsp;+&nbsp;2&nbsp;],&nbsp;1,&nbsp;0)&nbsp;=&nbsp;0) &nbsp;&nbsp;(str&nbsp;[&nbsp;0&nbsp;]&nbsp;=&nbsp;str&nbsp;[&nbsp;0&nbsp;]&nbsp;||&nbsp;','&nbsp;||&nbsp;str&nbsp;[&nbsp;iteration_number&nbsp;+&nbsp;1&nbsp;]) &nbsp;order&nbsp;by&nbsp;1; --------&nbsp;适用范围：8i,9i,10g及以后版本&nbsp;（&nbsp;MAX&nbsp;+&nbsp;DECODE&nbsp;） select&nbsp;t.id&nbsp;id,&nbsp;max(substr(sys_connect_by_path(t.name,&nbsp;','),&nbsp;2))&nbsp;str &nbsp;&nbsp;from&nbsp;(select&nbsp;id,&nbsp;name,&nbsp;row_number()&nbsp;over(partition&nbsp;by&nbsp;id&nbsp;order&nbsp;by&nbsp;name)&nbsp;rn &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;test)&nbsp;t &nbsp;start&nbsp;with&nbsp;rn&nbsp;=&nbsp;1 connect&nbsp;by&nbsp;rn&nbsp;=&nbsp;prior&nbsp;rn&nbsp;+&nbsp;1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and&nbsp;id&nbsp;=&nbsp;prior&nbsp;id &nbsp;group&nbsp;by&nbsp;t.id; </td> 
 </tr> 
 </tbody> 
</table>

### 懒人扩展用法: ###

**案例: **我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm\_concat来让这个需求变简单，假设我的APP\_USER表中有（id,username,password,age）4个字段。查询结果如下

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> /**&nbsp;这里的表名默认区分大小写&nbsp;*/ select&nbsp;'create&nbsp;or&nbsp;replace&nbsp;view&nbsp;as&nbsp;select&nbsp;'&nbsp;||&nbsp;wm_concat(column_name)&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'&nbsp;from&nbsp;APP_USER'&nbsp;sqlStr &nbsp;&nbsp;from&nbsp;user_tab_columns &nbsp;where&nbsp;table_name&nbsp;=&nbsp;'APP_USER'; </td> 
 </tr> 
 </tbody> 
</table>

![d2bbf01739100aa3a37a844d2be90873.png][]

利用系统表方式查询

SQL Code

## 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

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 6 7 8 9 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> create&nbsp;table&nbsp;demo(id&nbsp;int,name&nbsp;varchar(20),nums&nbsp;int);&nbsp;&nbsp;----&nbsp;创建表 insert&nbsp;into&nbsp;demo&nbsp;values(1,&nbsp;'苹果',&nbsp;1000); insert&nbsp;into&nbsp;demo&nbsp;values(2,&nbsp;'苹果',&nbsp;2000); insert&nbsp;into&nbsp;demo&nbsp;values(3,&nbsp;'苹果',&nbsp;4000); insert&nbsp;into&nbsp;demo&nbsp;values(4,&nbsp;'橘子',&nbsp;5000); insert&nbsp;into&nbsp;demo&nbsp;values(5,&nbsp;'橘子',&nbsp;3000); insert&nbsp;into&nbsp;demo&nbsp;values(6,&nbsp;'葡萄',&nbsp;3500); insert&nbsp;into&nbsp;demo&nbsp;values(7,&nbsp;'芒果',&nbsp;4200); insert&nbsp;into&nbsp;demo&nbsp;values(8,&nbsp;'芒果',&nbsp;5500); </td> 
 </tr> 
 </tbody> 
</table>

![c611b499db8a72d115feb52d8d3aaaf8.png][]

**分组查询 （**当然这是不符合查询一条数据的要求的）

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;name,&nbsp;sum(nums)&nbsp;nums&nbsp;from&nbsp;demo&nbsp;group&nbsp;by&nbsp;name </td> 
 </tr> 
 </tbody> 
</table>

![b5eb6ff1ab036b57c2fe75e6d15b7dce.png][]

**行转列查询**

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;*&nbsp;from&nbsp;(select&nbsp;name,&nbsp;nums&nbsp;from&nbsp;demo)&nbsp;pivot&nbsp;(sum(nums)&nbsp;for&nbsp;name&nbsp;in&nbsp;('苹果'&nbsp;苹果,&nbsp;'橘子',&nbsp;'葡萄',&nbsp;'芒果')); </td> 
 </tr> 
 </tbody> 
</table>

![2a148390988167058b181997790b50c9.png][]

**注意**： pivot（聚合函数 for 列名 in（类型）） ，其中 in('') 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers

当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;* &nbsp;&nbsp;from&nbsp;(select&nbsp;sum(nums)&nbsp;苹果&nbsp;from&nbsp;demo&nbsp;where&nbsp;name&nbsp;=&nbsp;'苹果'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(select&nbsp;sum(nums)&nbsp;橘子&nbsp;from&nbsp;demo&nbsp;where&nbsp;name&nbsp;=&nbsp;'橘子'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(select&nbsp;sum(nums)&nbsp;葡萄&nbsp;from&nbsp;demo&nbsp;where&nbsp;name&nbsp;=&nbsp;'葡萄'), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(select&nbsp;sum(nums)&nbsp;芒果&nbsp;from&nbsp;demo&nbsp;where&nbsp;name&nbsp;=&nbsp;'芒果'); </td> 
 </tr> 
 </tbody> 
</table>

### unpivot 行转列 ###

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

创建表和数据

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 6 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> create&nbsp;table&nbsp;Fruit(id&nbsp;int,name&nbsp;varchar(20),&nbsp;Q1&nbsp;int,&nbsp;Q2&nbsp;int,&nbsp;Q3&nbsp;int,&nbsp;Q4&nbsp;int); insert&nbsp;into&nbsp;Fruit&nbsp;values(1,'苹果',1000,2000,3300,5000); insert&nbsp;into&nbsp;Fruit&nbsp;values(2,'橘子',3000,3000,3200,1500); insert&nbsp;into&nbsp;Fruit&nbsp;values(3,'香蕉',2500,3500,2200,2500); insert&nbsp;into&nbsp;Fruit&nbsp;values(4,'葡萄',1500,2500,1200,3500); select&nbsp;*&nbsp;from&nbsp;Fruit </td> 
 </tr> 
 </tbody> 
</table>

![ecef74223081ba539d7799ebdfebe856.png][]

列转行查询

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;id&nbsp;,&nbsp;name,&nbsp;jidu,&nbsp;xiaoshou&nbsp;from&nbsp;Fruit&nbsp;unpivot&nbsp;(xiaoshou&nbsp;for&nbsp;jidu&nbsp;in&nbsp;(q1,&nbsp;q2,&nbsp;q3,&nbsp;q4)&nbsp;) </td> 
 </tr> 
 </tbody> 
</table>

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

![d23efb67061cf6daa6dedda26af3d297.png][]

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

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 6 7 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;id,&nbsp;name&nbsp;,'Q1'&nbsp;jidu,&nbsp;(select&nbsp;q1&nbsp;from&nbsp;fruit&nbsp;where&nbsp;id=f.id)&nbsp;xiaoshou&nbsp;from&nbsp;Fruit&nbsp;f union select&nbsp;id,&nbsp;name&nbsp;,'Q2'&nbsp;jidu,&nbsp;(select&nbsp;q2&nbsp;from&nbsp;fruit&nbsp;where&nbsp;id=f.id)&nbsp;xiaoshou&nbsp;from&nbsp;Fruit&nbsp;f union select&nbsp;id,&nbsp;name&nbsp;,'Q3'&nbsp;jidu,&nbsp;(select&nbsp;q3&nbsp;from&nbsp;fruit&nbsp;where&nbsp;id=f.id)&nbsp;xiaoshou&nbsp;from&nbsp;Fruit&nbsp;f union select&nbsp;id,&nbsp;name&nbsp;,'Q4'&nbsp;jidu,&nbsp;(select&nbsp;q4&nbsp;from&nbsp;fruit&nbsp;where&nbsp;id=f.id)&nbsp;xiaoshou&nbsp;from&nbsp;Fruit&nbsp;f </td> 
 </tr> 
 </tbody> 
</table>

### XML类型 ###

上述pivot列转行示例中，你已经知道了需要查询的类型有哪些，用in()的方式包含，假设如果您不知道都有哪些值，您怎么构建查询呢？

pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出，在此输出中，您可以指定一个特殊的子句 ANY 而非文字值

示例如下：

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:middle;"> 1 2 3 4 5 6 7 </td> 
 <td style="vertical-align:middle;"> &nbsp;&nbsp; </td> 
 <td style="vertical-align:middle;"> select&nbsp;*&nbsp;from&nbsp;( &nbsp;&nbsp;&nbsp;select&nbsp;name,&nbsp;nums&nbsp;as&nbsp;"Purchase&nbsp;Frequency" &nbsp;&nbsp;&nbsp;from&nbsp;demo&nbsp;t )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pivot&nbsp;xml&nbsp;( &nbsp;&nbsp;&nbsp;sum(nums)&nbsp;for&nbsp;name&nbsp;in&nbsp;(any) ) </td> 
 </tr> 
 </tbody> 
</table>

![a5dda82d9ab4a8cbcc759e386593f3bd.png][]

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

![8f0a6ced1bed45e645299422f0f9a0ba.png][]

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

SQL Code

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 </td> 
 <td style="vertical-align:middle;">&nbsp;</td> 
 <td> create&nbsp;or&nbsp;replace&nbsp;procedure&nbsp;ljz_pivot_xml_sp(pi_table_name&nbsp;&nbsp;&nbsp;varchar2, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pi_column_name&nbsp;&nbsp;varchar2, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pi_create_table&nbsp;varchar2)&nbsp;as &nbsp;&nbsp;v_column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;nvarchar2(50); &nbsp;&nbsp;v_count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;number&nbsp;:=&nbsp;0; &nbsp;&nbsp;v_i&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;number; &nbsp;&nbsp;v_parent_node&nbsp;nvarchar2(4000); &nbsp;&nbsp;v_child_node&nbsp;&nbsp;nvarchar2(4000); &nbsp;&nbsp;v_over&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;boolean&nbsp;:=&nbsp;false; &nbsp;&nbsp;v_tmp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;nvarchar2(50); &nbsp;&nbsp;v_existsnode&nbsp;&nbsp;number; &nbsp;&nbsp;v_sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;clob; &nbsp;&nbsp;v_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;varchar2(30); &nbsp;&nbsp;v_name_xml&nbsp;&nbsp;&nbsp;&nbsp;xmltype; begin &nbsp;&nbsp;v_sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:=&nbsp;'select&nbsp;x.*&nbsp;from&nbsp;'&nbsp;||&nbsp;pi_table_name&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'&nbsp;a,&nbsp;xmltable(''/PivotSet''&nbsp;passing&nbsp;a.'&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pi_column_name&nbsp;||&nbsp;'&nbsp;columns&nbsp;'; &nbsp;&nbsp;v_parent_node&nbsp;:=&nbsp;'/PivotSet'; &nbsp;&nbsp;v_child_node&nbsp;&nbsp;:=&nbsp;'item[1]/column[2]'; &nbsp;&nbsp;v_i&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:=&nbsp;1; &nbsp;&nbsp;execute&nbsp;immediate&nbsp;'select&nbsp;'&nbsp;||&nbsp;pi_column_name&nbsp;||&nbsp;'&nbsp;from&nbsp;'&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pi_table_name&nbsp;||&nbsp;'&nbsp;where&nbsp;rownum=1' &nbsp;&nbsp;&nbsp;&nbsp;into&nbsp;v_name_xml; &nbsp;&nbsp;select&nbsp;existsnode(v_name_xml, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'/PivotSet/item['&nbsp;||&nbsp;to_char(v_i)&nbsp;||&nbsp;']/column[1]') &nbsp;&nbsp;&nbsp;&nbsp;into&nbsp;v_existsnode &nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;dual; &nbsp;&nbsp;while&nbsp;v_existsnode&nbsp;=&nbsp;1&nbsp;loop &nbsp;&nbsp;&nbsp;&nbsp;execute&nbsp;immediate&nbsp;'select&nbsp;substr(extractvalue('&nbsp;||&nbsp;pi_column_name&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;',&nbsp;''/PivotSet/item['&nbsp;||&nbsp;to_char(v_i)&nbsp;||&nbsp;']/column[1]''),1,30) &nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;'&nbsp;||&nbsp;pi_table_name&nbsp;||&nbsp;'&nbsp;x' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;into&nbsp;v_name; &nbsp;&nbsp;&nbsp;&nbsp;v_sql&nbsp;:=&nbsp;v_sql&nbsp;||&nbsp;'"'&nbsp;||&nbsp;v_name&nbsp;||&nbsp;'"&nbsp;varchar2(30)&nbsp;path&nbsp;''item['&nbsp;|| &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;to_char(v_i)&nbsp;||&nbsp;']/column[2]'','; &nbsp;&nbsp;&nbsp;&nbsp;v_i&nbsp;&nbsp;&nbsp;:=&nbsp;v_i&nbsp;+&nbsp;1; &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;existsnode(v_name_xml, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'/PivotSet/item['&nbsp;||&nbsp;to_char(v_i)&nbsp;||&nbsp;']/column[1]') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;into&nbsp;v_existsnode &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;dual; &nbsp;&nbsp;end&nbsp;loop; &nbsp;&nbsp;v_sql&nbsp;:=&nbsp;trim(','&nbsp;from&nbsp;v_sql)&nbsp;||&nbsp;')&nbsp;x'; &nbsp;&nbsp;commit; &nbsp;&nbsp;select&nbsp;count(1) &nbsp;&nbsp;&nbsp;&nbsp;into&nbsp;v_count &nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;user_tab_columns &nbsp;&nbsp;&nbsp;where&nbsp;table_name&nbsp;=&nbsp;upper(pi_create_table); &nbsp;&nbsp;if&nbsp;v_count&nbsp;=&nbsp;0&nbsp;then &nbsp;&nbsp;&nbsp;&nbsp;execute&nbsp;immediate&nbsp;'create&nbsp;table&nbsp;'&nbsp;||&nbsp;pi_create_table&nbsp;||&nbsp;'&nbsp;as&nbsp;'&nbsp;||&nbsp;v_sql; &nbsp;&nbsp;end&nbsp;if; end; </td> 
 </tr> 
 </tbody> 
</table>

第一个参数为要解析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;

![33b607c514807ec77713f2cf2807536d.png][]

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

select extractvalue(name\_xml, '/PivotSet/item\[1\]/column\[1\]')

from (select \* from (select name,nums from demo) pivot xml(sum(nums) for name in(any))) x

where existsnode(name\_xml, '/PivotSet/item\[1\]/column\[1\]') = 1;

![aaf95894646a92f3b149b789ec1cd6f0.png][]

select x.\*

from (select \*

from (select name, nums from demo)

pivot xml(sum(nums)

for name in(any))) a,

xmltable('/PivotSet' passing a.name\_xml columns

芒果 varchar2(30) path 'item\[1\]/column\[2\]',

苹果 varchar2(30) path 'item\[2\]/column\[2\]') x

![cfbd20b5a6a43e9b120f371252976032.png][]

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

select '''' || listagg(substr(name, 1, 30), q'\{','\}') within group(order by name) || ''''

from (select distinct name from demo);

![01de6332c79b4ab9cc4e9747aa0a6797.png][]

select \*

from (select name, nums from demo)

pivot(sum(nums)

for name in('苹果', '橘子', '葡萄', '芒果'));

![9346f1bf84ce8ddcb8d03613e41dcdcc.png][]

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

## 结论 ##

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

[https://www.cnblogs.com/mellowsmile/p/4642306.html][https_www.cnblogs.com_mellowsmile_p_4642306.html]

[5461cbf16e1c94b1ee7b04cb8cbac32e.png]: /images/20221023/c289a13fade7497f94caf346f068ec86.png
[bcd9528f81477fd1ac40700025b9256a.png]: /images/20221023/4e7c889e8da94f468d366d393d803233.png
[1d880db3d413668622fc668d96fd15e3.png]: /images/20221023/49cc28ccbd0d4c078b4ae8e2a40b26e4.png
[d2bbf01739100aa3a37a844d2be90873.png]: /images/20221023/b61b91df9c2f4e3f95f233e33b07c144.png
[html]: http://www.2cto.com/kf/qianduan/css/
[c611b499db8a72d115feb52d8d3aaaf8.png]: /images/20221023/56abb8cd7a0a431bb15754eda2c67af8.png
[b5eb6ff1ab036b57c2fe75e6d15b7dce.png]: /images/20221023/2f44b7ac6610499c8856683fab68fb2e.png
[2a148390988167058b181997790b50c9.png]: /images/20221023/6706e22b53d0447e86dff5861f623907.png
[ecef74223081ba539d7799ebdfebe856.png]: /images/20221023/12273093555d495aaf475cc0aa2880d0.png
[d23efb67061cf6daa6dedda26af3d297.png]: /images/20221023/9d237a60508748edbcfa8d38c4d433ad.png
[a5dda82d9ab4a8cbcc759e386593f3bd.png]: /images/20221023/6417eff3690a4f6894111e11f78610f2.png
[8f0a6ced1bed45e645299422f0f9a0ba.png]: /images/20221023/dbecef618af940258cc984368d43b19e.png
[33b607c514807ec77713f2cf2807536d.png]: /images/20221023/70a9f2cc7aa34c47afdceeec64393653.png
[aaf95894646a92f3b149b789ec1cd6f0.png]: /images/20221023/c6dd2f1f9f654260a79d706af61c15e6.png
[cfbd20b5a6a43e9b120f371252976032.png]: /images/20221023/eff5dd895d914963a06236d883df9120.png
[01de6332c79b4ab9cc4e9747aa0a6797.png]: /images/20221023/b99ed2ab45ac45b6b668c1bb49da4050.png
[9346f1bf84ce8ddcb8d03613e41dcdcc.png]: /images/20221023/78f9eeddd9e643dfb47a7b583488220f.png
[https_www.cnblogs.com_mellowsmile_p_4642306.html]: https://www.cnblogs.com/mellowsmile/p/4642306.html