SQL 行转列列转行 Oracle转置函数函数pivot、unpivot 解决wm_concat 没有排序

比眉伴天荒 2022-04-10 14:42 368阅读 0赞

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

# [HH][] #

## 终风且暴，顾我则笑，谑浪笑敖，中心是悼。 终风且霾，惠然肯来，莫往莫来，悠悠我思。 ##

*  [博客园][Link 1]
 *  [首页][HH]
 *  [新随笔][Link 2]
 *  [联系][Link 3]
 *  [订阅][Link 4]
 *  [管理][Link 5]

随笔 - 48  文章 - 0  评论 - 12

# [Oracle行转列、列转行的Sql语句总结][https_www.cnblogs.com_mellowsmile_p_4642306.html] #

## 多行转字符串 ##

这个比较简单，用||或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

![131053461735696.png][]

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

SQL Code

![131053487514008.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>

![131053504541221.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>

![131053526733577.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>

![131054006573970.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>

![131054016732028.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>

![131054022669211.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>

![131054041578367.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字段也是临时的变量

![131054060487524.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>

![131054083606150.png][]

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

![131054099543864.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;

![151511355162717.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;

![151511366419273.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

![151511377518302.png][]

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

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

from (select distinct name from demo);

![151511388602629.png][]

select \*

from (select name, nums from demo)

pivot(sum(nums)

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

![151511396264229.png][]

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

## 结论 ##

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

分类: [Oracle 相关][Oracle]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![20150331193659.png][]][20150331193659.png 1]

[mellowsmile][20150331193659.png 1]  
[关注 - 3][- 3]  
[粉丝 - 24][- 24]

\+加关注

[« ][Link 6]上一篇：[Oracle发送邮件，支持HTML，多收件人，多附件][Link 6]  
[» ][Link 7]下一篇：[Oracle用法、函数备忘记录][Link 7]

posted @ 2015-07-13 10:54 [mellowsmile][HH] 阅读(134290) 评论(7) [编辑][Link 8] [收藏][Link 9]

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

[https://blog.csdn.net/wyqwilliam/article/details/82559023][https_blog.csdn.net_wyqwilliam_article_details_82559023]

原

# SQL行转列、列转行 #

2018年09月09日 15:51:14 [道法—自然][Link 10] 阅读数：182

SQL行转列、列转行

这个主题还是比较常见的，行转列主要适用于对数据作聚合统计，如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。

一、整理测试数据

1.  `create table wyc_test(`
2.  `id int(32) not null auto_increment,`
3.  `name varchar(80) default null,`
4.  `date date default null,`
5.  `scount int(32),`
6.  `primary key (id)`
7.  `);`
8.  ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (1,'小说','2013-09-01',10000);``
9.  ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (2,'微信','2013-09-01',20000);``
10. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (3,'小说','2013-09-02',30000);``
11. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (4,'微信','2013-09-02',35000);``
12. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (5,'小说','2013-09-03',31000);``
13. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (6,'微信','2013-09-03',36000);``
14. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (7,'小说','2013-09-04',35000);``
15. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (8,'微信','2013-09-04',38000);``
16. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (9,'小说','2013-09-01',80000);``
17. ``INSERT INTO `wyc_test` (`id`,`name`,`date`,`scount`) VALUES (10,'微信','2013-09-01',70000);``

**二、行转列**

主要思路是分组后使用case进行条件判断处理

1.  `#行转列`
2.  `select`
3.  `a.date,`
4.  `sum(case a.name`
5.  `when '小说' then a.scount`
6.  `else 0`
7.  `end) 'sum_小说',`
8.  `max(case a.name`
9.  `when '小说' then a.scount`
10. `else 0`
11. `end) 'max_小说',`
12. `sum(case a.name`
13. `when '微信' then a.scount`
14. `else 0`
15. `end) '微信',`
16. `max(case a.name`
17. `when '小说' then a.scount`
18. `else 0`
19. `end) 'max_微信'`
20. `from`
21. `wyc_test a`
22. `group by date;`

结果：

![20141127225216359][]

**三、列转行**

主要思路也是分组后使用case

1.  `#列转行`
2.  `select`
3.  `a.date,`
4.  `concat('小说:',`
5.  `cast(sum(case a.name`
6.  `when '小说' then a.scount`
7.  `else 0`
8.  `end)`
9.  `as char),`
10. `'微信',`
11. `cast(sum(case a.name`
12. `when '微信' then a.scount`
13. `else 0`
14. `end)`
15. `as char)) as 'str'`
16. `from`
17. `wyc_test a`
18. `group by a.date;`
19. `#列转行`
20. `#1.使用mysql提供的函数分组`
21. `select a.date,group_concat(a.name,'总量：', a.scount) from wyc_test a group by a.date,a.name;`
22. `#2.使用mysql提供的函数分组`
23. `select a.date,a.name, group_concat(a.name, '总量：', a.scount) from wyc_test a group by a.date,a.name;`
24. `#3.普通group结合字符串拼接`
25. `SELECT`
26. `a.date,`
27. `concat('小说总量：',`
28. `cast(sum(case a.name`
29. `when '小说' then a.scount`
30. `else 0`
31. `end)`
32. `as char)) as '小说',`
33. `concat('微信总量：',`
34. `cast(sum(case a.name`
35. `when '微信' then a.scount`
36. `else 0`
37. `end)`
38. `as char)) as '微信'`
39. `from`
40. `wyc_test a`
41. `group by a.date;`

结果：  
![20141127225221328][]

**四、列转行详解**  
   
1.1、初始测试数据  
        表结构：TEST\_TB\_GRADE2  
Sql代码   
create table TEST\_TB\_GRADE2   
(   
  ID         NUMBER(10) not null,   
  USER\_NAME  VARCHAR2(20 CHAR),   
  CN\_SCORE   FLOAT,   
  MATH\_SCORE FLOAT,   
  EN\_SCORE   FLOAT   
)   
   
        初始数据如下图：

![20150612171046814][]    
   
1.2、 如果需要实现如下的查询效果图：

![20150612171101390][]                      
   
这就是最常见的列转行，主要原理是利用SQL里面的union，具体的sql语句如下：  
Sql代码   
select user\_name, '语文' COURSE , CN\_SCORE as SCORE from test\_tb\_grade2    
union select user\_name, '数学' COURSE, MATH\_SCORE as SCORE from test\_tb\_grade2    
union select user\_name, '英语' COURSE, EN\_SCORE as SCORE from test\_tb\_grade2    
order by user\_name,COURSE    
   
 也可以利用【 insert all into ... select 】来实现，首先需要先建一个表TEST\_TB\_GRADE3：  
Sql代码   
create table TEST\_TB\_GRADE3     
    (    
      USER\_NAME VARCHAR2(20 CHAR),     
      COURSE    VARCHAR2(20 CHAR),     
      SCORE     FLOAT     
    )     
 再执行下面的sql：  
   
Sql代码   
insert all   
into test\_tb\_grade3(USER\_NAME,COURSE,SCORE) values(user\_name, '语文', CN\_SCORE)   
into test\_tb\_grade3(USER\_NAME,COURSE,SCORE) values(user\_name, '数学', MATH\_SCORE)   
into test\_tb\_grade3(USER\_NAME,COURSE,SCORE) values(user\_name, '英语', EN\_SCORE)   
select user\_name, CN\_SCORE, MATH\_SCORE, EN\_SCORE from test\_tb\_grade2;   
commit;

别忘记commit操作，然后再查询TEST\_TB\_GRADE3，发现表中的数据就是列转成行了。

[https://blog.csdn.net/wyqwilliam/article/details/82559023][https_blog.csdn.net_wyqwilliam_article_details_82559023]

[https://www.cnblogs.com/wayne-ivan/p/6416489.html][https_www.cnblogs.com_wayne-ivan_p_6416489.html]

[博客园][Link 1]  [首页][Link 11]  [新随笔][Link 2]  [联系][Link 12]  [管理][Link 5]  [订阅][Link 13] [![订阅][xml.gif]][Link 13]

# [wm\_concat函数的排序问题][https_www.cnblogs.com_wayne-ivan_p_6416489.html] #

wm\_concat在行转列的时候非常有用，但在行转列的过程中的排序问题常常难以控制。

可见下面例子：  
准备测试表：  
drop table t;  
create table t (n number,m number);  
insert into t values(1,1);  
insert into t values(5,3);  
insert into t values(3,3);  
insert into t values(6,5);  
insert into t values(7,2);  
insert into t values(2,2);  
insert into t values(0,1);  
insert into t values(11,1);  
insert into t values(15,3);  
insert into t values(13,3);  
insert into t values(16,5);  
insert into t values(17,2);  
insert into t values(12,2);  
insert into t values(10,1);  
commit;

SQL> select \* from t order by 2,1;

N M  
———- ———-  
0 1  
1 1  
10 1  
11 1  
2 2  
7 2  
12 2  
17 2  
3 3  
5 3  
13 3  
15 3  
6 5  
16 5

测试wm\_concat后的顺序：  
测试1：  
SQL> select m,wm\_concat(n) from t group by m;

M WM\_CONCAT(N)  
———- ——————————————————————————–  
1 11,0,1,10  
2 17,2,7,12  
3 15,3,5,13  
5 16,6

可见wm\_concat后的顺序并没有按大->小，或小->大的顺序。

测试2：  
–参考网上一些解决思路  
SQL> select m,wm\_concat(n)  
2 from (select n,m from t order by m,n )  
3 group by m;

M WM\_CONCAT(N)  
———- ——————————————————————————–  
1 0,11,10,1  
2 2,17,12,7  
3 3,15,13,5  
5 6,16

可见顺序问题还是没有解决

最终解决思路：  
SQL> select m, max(r)  
2 from (select m, wm\_concat(n) over (partition by m order by n) r from t)  
3 group by m ;

M MAX(R)  
———- ——————————————————————————–  
1 0,1,10,11  
2 2,7,12,17  
3 3,5,13,15  
5 6,16

分类: [Oracle][Oracle 1]

好文要顶 关注我 收藏该文 ![icon_weibo_24.png][] ![wechat.png][]

[![sample_face.gif][]][sample_face.gif 1]

[伊凡][sample_face.gif 1]  
[关注 - 0][- 0]  
[粉丝 - 86][- 86]

\+加关注

[« ][Link 14]上一篇：[Oracle树查询及相关函数][Link 14]  
[» ][Link 15]下一篇：[Oracle系统表整理+常用SQL语句收集][Link 15]

posted @ 2017-02-19 18:23 [伊凡][Link 11] 阅读(8665) 评论(0) [编辑][Link 16] [收藏][Link 17]

[https://www.cnblogs.com/wayne-ivan/p/6416489.html][https_www.cnblogs.com_wayne-ivan_p_6416489.html]

[https://blog.csdn.net/seandba/article/details/72730657][https_blog.csdn.net_seandba_article_details_72730657]

原

# Oracle SQL函数pivot、unpivot转置函数实现行转列、列转行 #

2017年05月25日 16:18:27 [Seandba][] 阅读数：26664

函数PIVOT、UNPIVOT转置函数实现行转列、列转行，效果如下图所示：

1.PIVOT为行转列，从图示的左边到右边

2.UNPIVOT为列转行，从图示的右边到左边

3.左边为纵表，结构简单，易扩展

4.右边为横表，展示清晰，方便查询

5.很多时候业务表为纵表，但是统计分析需要的结果如右边的横表，这时候就需要用到转置函数了

示例图表：

![20170525162021933][]

Pivot语法：

1. `SELECT ....`
2. `FROM <table-expr>`
3. `PIVOT`
4. `(`
5. `aggregate-function(<column>)`
6. `FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)`
7. `) AS <alias>`
8. `WHERE .....`

注意：

FOR <pivot-column>

这个是不支持表达式的，如果需要，请通过子查询或者视图先预处理。

### Pivot ###

例子1：先构造一个子查询，然后根据CHANNEL列进行转置，源表sales\_view里面可能有很多列，不需要列先通过子查询过滤掉再进行转置。

另外转置后的列指定了别名，值是对amount\_sold列的汇总。

1.  `SELECT * FROM`
2.  `(SELECT product, channel, amount_sold`
3.  `FROM sales_view`
4.  `) S PIVOT (SUM(amount_sold)`
5.  `FOR CHANNEL IN (3 AS DIRECT_SALES, 4 AS INTERNET_SALES,`
6.  `5 AS CATALOG_SALES, 9 AS TELESALES))`
7.  `ORDER BY product;`
8.   
9.  `PRODUCT DIRECT_SALES INTERNET_SALES CATALOG_SALES TELESALES`
10. `---------------------- ------------ -------------- ------------- ---------`
11. `...`
12. `Internal 6X CD-ROM 229512.97 26249.55`
13. `Internal 8X CD-ROM 286291.49 42809.44`
14. `Keyboard Wrist Rest 200959.84 38695.36 1522.73`
15. `...`

例子2：基于多列进行转置，下面例子是基于channel、quarter两列进行转置

1.  `SELECT *`
2.  `FROM`
3.  `(SELECT product, channel, quarter, quantity_sold`
4.  `FROM sales_view`
5.  `) PIVOT (SUM(quantity_sold)`
6.  `FOR (channel, quarter) IN`
7.  `((5, '02') AS CATALOG_Q2,`
8.  `(4, '01') AS INTERNET_Q1,`
9.  `(4, '04') AS INTERNET_Q4,`
10. `(2, '02') AS PARTNERS_Q2,`
11. `(9, '03') AS TELE_Q3`
12. `)`
13. `);`
14.  
15. `PRODUCT CATALOG_Q2 INTERNET_Q1 INTERNET_Q4 PARTNERS_Q2 TELE_Q3`
16. `------- ---------- ----------- ----------- ----------- -------`
17. `...`
18. `Bounce 347 632 954`
19. `...`
20. `Smash Up Boxing 129 280 560`
21. `...`
22. `Comic Book Heroes 47 155 275`
23. `...`

例子3：对多列的值进行汇总计算，以下是基于channel例进行转置，然后对amount\_sold和quantity\_sold两列进行合计运算

1.  `SELECT *`
2.  `FROM`
3.  `(SELECT product, channel, amount_sold, quantity_sold`
4.  `FROM sales_view`
5.  `) PIVOT (SUM(amount_sold) AS sums,`
6.  `SUM(quantity_sold) AS sumq`
7.  `FOR channel IN (5, 4, 2, 9)`
8.  `)`
9.  `ORDER BY product;`
10.  
11. `PRODUCT 5_SUMS 5_SUMQ 4_SUMS 4_SUMQ 2_SUMS 2_SUMQ 9_SUMS 9_SUMQ`
12. `------------- ------ ------ ------ ------ ------ ------ ------ ------`
13. `O/S Doc Set English 142780.36 3081 381397.99 8044 6028.66 134`
14. `O/S Doc Set French 55503.58 1192 132000.77 2782`
15. `...`

## ##

### Unpivot ###

unpivot是pivot的相反操作，进行的是列转行

例子1：先看源表结构，for子句指定将（Q1\_SUMQ, Q2\_SUMQ, Q3\_SUMQ, Q4\_SUMQ）这4列转置为行，

for子句之前的quantity\_sold是4列转置后的列名，

decode还定义了每列转置为行后新标示列的值，这个等下看第2个例子可以看到，也可以在 in 子句后面加 as 指定别名。

UNPIVOT INCLUDE NULLS 指定空值也进行转置，如果是EXCLUDE NULLS 将忽略空值。

1.  `SELECT *`
2.  `FROM pivotedTable`
3.  `ORDER BY product;`
4.   
5.  `PRODUCT Q1_SUMQ Q1_SUMA Q2_SUMQ Q2_SUMA Q3_SUMQ Q3_SUMA Q4_SUMQ Q4_SUMA`
6.  `--------------- ------- ------- ------- -------- ------- -------- ------- ---------`
7.  `1.44MB External 6098 58301.33 5112 49001.56 6050 56974.3 5848 55341.28`
8.  `128MB Memory 1963 110763.63 2361 132123.12 3069 170710.4 2832 157736.6`
9.  `17" LCD 1492 1812786.94 1387 1672389.06 1591 1859987.66 1540 1844008.11`

1.  `SELECT product, DECODE(quarter, 'Q1_SUMQ', 'Q1', 'Q2_SUMQ', 'Q2', 'Q3_SUMQ', 'Q3',`
2.  `'Q4_SUMQ', 'Q4') AS quarter, quantity_sold`
3.  `FROM pivotedTable`
4.  `UNPIVOT INCLUDE NULLS`
5.  `(quantity_sold`
6.  `FOR quarter IN (Q1_SUMQ, Q2_SUMQ, Q3_SUMQ, Q4_SUMQ))`
7.  `ORDER BY product, quarter;`
8.   
9.  `PRODUCT QUARTER QUANTITY_SOLD`
10. `------- -- -------------`
11. `1.44MB External 3.5" Diskette Q1 6098`
12. `1.44MB External 3.5" Diskette Q2 5112`
13. `1.44MB External 3.5" Diskette Q3 6050`
14. `1.44MB External 3.5" Diskette Q4 5848`
15. `128MB Memory Card Q1 1963`
16. `128MB Memory Card Q2 2361`
17. `128MB Memory Card Q3 3069`
18. `128MB Memory Card Q4 2832`
19. `...`

例子2：转置多列的情况

1.  `SELECT product, quarter, quantity_sold, amount_sold`
2.  `FROM pivotedTable`
3.  `UNPIVOT INCLUDE NULLS`
4.  `(`
5.  `(quantity_sold, amount_sold)`
6.  `FOR quarter IN ((Q1_SUMQ, Q1_SUMA) AS 'Q1', (Q2_SUMQ, Q2_SUMA) AS 'Q2', (Q3_SUMQ, Q3_SUMA) AS 'Q3', (Q4_SUMQ, Q4_SUMA) AS 'Q4'))`
7.  `ORDER BY product, quarter;`
8.   
9.  `PRODUCT QU QUANTITY_SOLD AMOUNT_SOLD`
10. `----------------------------- -- ------------- ------------`
11. `1.44MB External 3.5" Diskette Q1 6098 58301.33`
12. `1.44MB External 3.5" Diskette Q2 5112 49001.56`
13. `1.44MB External 3.5" Diskette Q3 6050 56974.3`
14. `1.44MB External 3.5" Diskette Q4 5848 55341.28`
15. `128MB Memory Card Q1 1963 110763.63`
16. `128MB Memory Card Q2 2361 132123.12`
17. `128MB Memory Card Q3 3069 170710.4`
18. `128MB Memory Card Q4 2832 157736.6`

总结，基本上按照语法套用即可，注意将源表非相关列先过滤掉，可是是子查询，也可以是视图。

最后试试解决这个问题吧，看你是否真的懂了！

http://blog.csdn.net/seandba/article/details/72629724

以上内容均来自Oracle11g官方文档，我只是搬运工。。。

**Oracle® Database Data Warehousing Guide  
11*g* Release 2 (11.2)**  
E25554-01

阅读更多  收藏

[https://blog.csdn.net/seandba/article/details/72730657][https_blog.csdn.net_seandba_article_details_72730657]

[https://segmentfault.com/q/1010000009622091?sort=created][https_segmentfault.com_q_1010000009622091_sort_created]

# [Oracle pivot in子查询的疑问][Oracle pivot in] #

在使用pivot函数进行行转列的时候 in后面接子查询就报错

![20190113150226184.png][]

按照oracle的文档，pivot语句中in后面的列如果不固定，只能使用xml格式的返回结果，如：  
[http://www.oracle-developer.n...][http_www.oracle-developer.n...]

SQL> SELECT *
 2 FROM pivot_data
 3 PIVOT XML
 4 (SUM(sal) FOR deptno IN (ANY));
 
 JOB DEPTNO_XML
 --------- ---------------------------------------------------------------------------
 ANALYST <PivotSet><item><column name = "DEPTNO">20</column><column name = "SUM(SAL)
 ">6600</column></item></PivotSet>
 
 CLERK <PivotSet><item><column name = "DEPTNO">10</column><column name = "SUM(SAL)
 ">1430</column></item><item><column name = "DEPTNO">20</column><column name
 = "SUM(SAL)">2090</column></item><item><column name = "DEPTNO">30</column>
 <column name = "SUM(SAL)">1045</column></item></PivotSet>
 
 MANAGER <PivotSet><item><column name = "DEPTNO">10</column><column name = "SUM(SAL)
 ">2695</column></item><item><column name = "DEPTNO">20</column><column name
 = "SUM(SAL)">3272.5</column></item><item><column name = "DEPTNO">30</colum
 n><column name = "SUM(SAL)">3135</column></item></PivotSet>

如果不想要xml格式的结果，就只能使用把in之间的内容提前计算出来，动态拼接成sql语句，使用execute immediately动态执行。

*   评论 · 2
 *  赞赏
 *  编辑

[![user-64.png][]][user-64.png 1]

[邢爱明][user-64.png 1] ![rp.svg][] 4.8k

[2017-06-05 回答][2017-06-05]

[https://segmentfault.com/q/1010000009622091?sort=created][https_segmentfault.com_q_1010000009622091_sort_created]

[https://zhidao.baidu.com/question/2203556378984153268.html?qbl=relate\_question\_0&word=pivot%20for%20in%20%D6%D0%BB%B9%BF%C9%D2%D4%D6%B8%B6%A8%D7%D3%B2%E9%D1%AF][https_zhidao.baidu.com_question_2203556378984153268.html_qbl_relate_question_0_word_pivot_20for_20in_20_D6_D0_BB_B9_BF_C9_D2_D4_D6_B8_B6_A8_D7_D3_B2_E9_D1_AF]

# oracle中pivot子查询如何用 *50* #

在oracle中使用pivot函数，看介绍说在in（）中可以使用子查询，但是使用子查询执行时报错缺少表达式，请问子查询应该如何使用，是不是pivot不支持子查询，谢谢

![gift.7b31617.gif_1547269129][] 我来答

*举报*浏览 6787 次

*  你的回答被采纳后将获得：
 *  系统奖励15（财富值+成长值）+难题奖励20（财富值+成长值）+提问者悬赏50（财富值+成长值）

1个回答

[\#今日科普\# 又到公司年会，有哪些给力的小游戏？][Link 18]

![00736368656e67636379323031306d11.jpg][]chengccy2010   
来自电脑网络类芝麻团 2015-05-22

xml 类型的时候可以使用any 关键字和子查询,返回的结果是xml结构的;  
  
select \* from (  
select times\_purchased as "Puchase Frequency", state\_code  
from customers t  
)  
pivot xml  
(  
count(state\_code)  
for state\_code in (select state\_code from preferred\_states)  
)  
order by 1

本回答被网友采纳

**7**   **67**

*举报*

[https_www.cnblogs.com_mellowsmile_p_4642306.html]: https://www.cnblogs.com/mellowsmile/p/4642306.html
[HH]: https://www.cnblogs.com/mellowsmile/
[Link 1]: https://www.cnblogs.com/
[Link 2]: https://i.cnblogs.com/EditPosts.aspx?opt=1
[Link 3]: https://msg.cnblogs.com/send/mellowsmile
[Link 4]: https://www.cnblogs.com/mellowsmile/rss
[Link 5]: https://i.cnblogs.com/
[131053461735696.png]: /images/20220328/28ce15b282e04db1870d0d11921fca59.png
[131053487514008.png]: /images/20220328/71d2f4911a754941b1a63611080d73af.png
[131053504541221.png]: /images/20220328/34910ce82c644dc7a57611e7646fd7f8.png
[131053526733577.png]: /images/20220328/8baf5f60ba534fabbda6bc29336061ea.png
[html]: http://www.2cto.com/kf/qianduan/css/
[131054006573970.png]: /images/20220328/f01d1b92887948f19f426e1af805c01e.png
[131054016732028.png]: /images/20220328/ecdd4ab94e5f4a709ab3c79f4d4eb866.png
[131054022669211.png]: /images/20220328/1c1fbf1190ed4391a96a6aa92f175d5a.png
[131054041578367.png]: /images/20220328/cbe8d43db01d4e62a6da236b0c3c738f.png
[131054060487524.png]: /images/20220328/0eab17d59e214cc99eabc5c0a9185e2a.png
[131054083606150.png]: /images/20220328/ed841f30afae4c20807a4f2566fba76a.png
[131054099543864.png]: /images/20220328/7637320b25f144f2bbd4573348db704a.png
[151511355162717.png]: /images/20220328/fbe070ea5f994d1588596557616c2c17.png
[151511366419273.png]: /images/20220328/4833aa0382344584999bfc8c12e055c3.png
[151511377518302.png]: /images/20220328/8f3d290a6c444e6ca0293f6e7fc5c4d7.png
[151511388602629.png]: /images/20220328/2e6bfd072e2843a0b1d1360f552dffb8.png
[151511396264229.png]: /images/20220328/964c03ddce1440a0b0222cea907cde44.png
[Oracle]: https://www.cnblogs.com/mellowsmile/category/673180.html
[icon_weibo_24.png]: https://common.cnblogs.com/images/icon_weibo_24.png
[wechat.png]: /images/20220328/6cfadcb2440f4fa6986f1794d72266c2.png
[20150331193659.png]: /images/20220328/af4f692a94e04d299f619783b57e5169.png
[20150331193659.png 1]: http://home.cnblogs.com/u/mellowsmile/
[- 3]: http://home.cnblogs.com/u/mellowsmile/followees
[- 24]: http://home.cnblogs.com/u/mellowsmile/followers
[Link 6]: https://www.cnblogs.com/mellowsmile/p/4616612.html
[Link 7]: https://www.cnblogs.com/mellowsmile/p/4643112.html
[Link 8]: https://i.cnblogs.com/EditPosts.aspx?postid=4642306
[Link 9]: https://www.cnblogs.com/mellowsmile/p/4642306.html#
[https_blog.csdn.net_wyqwilliam_article_details_82559023]: https://blog.csdn.net/wyqwilliam/article/details/82559023
[Link 10]: https://me.csdn.net/wyqwilliam
[20141127225216359]: /images/20220328/3d9c2255aa3246358410b86857b62866.png
[20141127225221328]: /images/20220328/8e6dce496c284bceb0ac76a06cb2649f.png
[20150612171046814]: /images/20220328/158648bfca104904b88b3ddd88c26325.png
[20150612171101390]: /images/20220328/a4e6dd120aab41ddbc0cb9ec98576df3.png
[https_www.cnblogs.com_wayne-ivan_p_6416489.html]: https://www.cnblogs.com/wayne-ivan/p/6416489.html
[Link 11]: https://www.cnblogs.com/wayne-ivan/
[Link 12]: https://msg.cnblogs.com/send/%E4%BC%8A%E5%87%A1
[Link 13]: /images/20220328/67b586d76ba44e4d9015050c3b6bae23.png
[xml.gif]: https://www.cnblogs.com/images/xml.gif
[Oracle 1]: https://www.cnblogs.com/wayne-ivan/category/100682.html
[sample_face.gif]: /images/20220328/a43f94424d224893bc295df0f067c478.png
[sample_face.gif 1]: http://home.cnblogs.com/u/wayne-ivan/
[- 0]: http://home.cnblogs.com/u/wayne-ivan/followees
[- 86]: http://home.cnblogs.com/u/wayne-ivan/followers
[Link 14]: https://www.cnblogs.com/wayne-ivan/p/6416486.html
[Link 15]: https://www.cnblogs.com/wayne-ivan/p/6416533.html
[Link 16]: https://i.cnblogs.com/EditPosts.aspx?postid=6416489
[Link 17]: https://www.cnblogs.com/wayne-ivan/p/6416489.html#
[https_blog.csdn.net_seandba_article_details_72730657]: https://blog.csdn.net/seandba/article/details/72730657
[Seandba]: https://me.csdn.net/Seandba
[20170525162021933]: /images/20220328/a4b808dfc23d42d18e50cf629b751808.png
[https_segmentfault.com_q_1010000009622091_sort_created]: https://segmentfault.com/q/1010000009622091?sort=created
[Oracle pivot in]: https://segmentfault.com/q/1010000009622091
[20190113150226184.png]: /images/20220328/cef5430452d64d219ab6c2a4eaa7f9e9.png
[http_www.oracle-developer.n...]: http://www.oracle-developer.net/display.php?id=506
[user-64.png]: /images/20220328/0861928cd5564ce19b21bacfbb85b5a0.png
[user-64.png 1]: https://segmentfault.com/u/xingaiming
[rp.svg]: /images/20220328/1e476e7b7b2945e783ea729bacb53144.png
[2017-06-05]: https://segmentfault.com/q/1010000009622091/a-1020000009656991
[https_zhidao.baidu.com_question_2203556378984153268.html_qbl_relate_question_0_word_pivot_20for_20in_20_D6_D0_BB_B9_BF_C9_D2_D4_D6_B8_B6_A8_D7_D3_B2_E9_D1_AF]: https://zhidao.baidu.com/question/2203556378984153268.html?qbl=relate_question_0&word=pivot%20for%20in%20%D6%D0%BB%B9%BF%C9%D2%D4%D6%B8%B6%A8%D7%D3%B2%E9%D1%AF
[gift.7b31617.gif_1547269129]: /images/20220328/ed948bd702c94702ad208823b8a3a457.png
[Link 18]: https://zhidao.baidu.com/question/210352040.html?entry=hottopic
[00736368656e67636379323031306d11.jpg]: /images/20220328/c2a1718eea254b22a122ae31a1626628.png