Oracle 11 SQL:有没有一种方法可以将1行拆分为x行 [英] Oracle 11 SQL : Is there a way to split 1 row into x rows

问题描述

客户要求将Oracle DB SQL中的1行拆分为6行.

Customer asked to Split 1 row from the Oracle DB SQL into 6 rows.

比方说，最初，SQL(具有多个联接的复杂sql等)提取了9列:
从X，Y，Z中选择A，B，C，D，E，F，G，H，I. . . (但查询非常复杂)

Let's say, originally the SQL (complex sql with multiple joins , etc) is pulling in 9 columns:
select A, B, C, D, E, F, G, H, I from X, Y, Z . . . (but quite complex query)

1)A，B，C，D，E，F，G，H，I.

1) A, B, C, D, E, F, G, H, I.

现在，客户要求返回返回到上述模式的每一行，新的输出应如下所示:
1)A，B，C，'D'，D
2)A，B，C，'E'，E
3)A，B，C，'F'，F
4)A，B，C，'G'，G
5)A，B，C，'H'，H
6)A，B，C，我"，我

Now, customer is asking for every row returning above pattern, the new output should be like below :
1) A, B, C, 'D', D
2) A, B, C, 'E', E
3) A, B, C, 'F', F
4) A, B, C, 'G', G
5) A, B, C, 'H', H
6) A, B, C, 'I', I

基本上，第1 3列将在所有6个新行中重复.
对于原始查询中的每一行都重复该过程.

Basically, the 1st 3 columns will be repeated in all the 6 NEW ROWS.
The procedure repeats for every row in the original query.

这可能吗?如果是，怎么办?

Is this possible ? If yes, how ?

推荐答案

您只需要unpivot子句即可垂直显示数据:

You just need unpivot clause to show the data vertically :

with t(a,b,c,d,e,f,g,h,i) as
(
 select 1,2,3,'D','E',2,3,'X','Y' from dual 
)
select a,b,c,val from
(
select a,b,c,to_char(d) as d, to_char(e) as e, to_char(f) as f, to_char(g) as g, 
             to_char(h) as h, to_char(i) as i
  from t
)  
unpivot 
( val for col in (d,e,f,g,h,i) )
order by col

演示

to_char()转换的实现是为了避免 ORA-01790: 表达式必须具有与相应表达式相同的数据类型错误.

to_char() conversions are implemented against getting ORA-01790: expression must have same datatype as corresponding expression error.

这篇关于Oracle 11 SQL:有没有一种方法可以将1行拆分为x行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！