Oracle 11 SQL:将1行拆分为x行并插入新列 [英] Oracle 11 SQL : Split 1 row into x rows and insert a new column
问题描述
我问了 Oracle 11 SQL:有没有一种方法可以将1行拆分为x行-这个问题与之非常接近，但是有点小...
I asked Oracle 11 SQL : Is there a way to split 1 row into x rows -- this question is very close to that but has a small twist ...
客户要求将Oracle DB SQL中的1行拆分为6行.
Customer asked to Split 1 row from the Oracle DB SQL into 6 rows.
比方说，最初，SQL(具有多个联接的复杂sql等)提取了9列:
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 . . .
(但查询非常复杂)
1) A, B, C, D, E, F, G, H, I.
现在，客户要求返回返回到上述模式的每一行，新的输出应如下所示:
Now, customer is asking for every row returning above pattern, the new output should be like below :
1) A, B, C, 'Name for D : ', D
2) A, B, C, 'Name for E : ', E
3) A, B, C, 'Name for F : ', F
4) A, B, C, 'Name for G : ', G
5) A, B, C, 'Name for H : ', H
6) A, B, C, 'Name for I : ', I
基本上，第1 3列的值将在所有6个新行中重复.
新行的第4列将是一个字符串，其中说明了第5列的含义.
Basically, the 1st 3 column values will be repeated in all the 6 New Rows.
The 4th column in the new row will be a string that says what the 5th column is about.
针对原始查询中的每一行重复该过程.
The procedure repeats for every row in the original query.
从前面的答案中，我知道unpivot可以做到这一点-只是无法自己解决这个问题.
From the earlier answer, I know unpivot can do this -- just not able to wrangle this out myself.
更新:
实际上，在我的问题中，我不清楚我想要的第4列的输出不是直接的串联.如果真是这样，我本可以自己做的.这些值将不是D，E，F，G，H，I的字面串联.
D，E，F，G，H，I的第四列值如下? :
列侬
保罗·麦卡特尼，
林戈·斯塔尔，
乔治·哈里森，
皮特·贝斯特(Pete Best)，
汤米·摩尔
Actually, I wasn't clear in my question that the output for Column 4 that I wanted was not a straightaway concatenation. If that was the case, I could have done it myself. These values will not be a literal concatenation of D,E,F,G,H,I.
How about the 4th column values for D,E,F,G,H,I are the follows ? :
Lennon,
paul McCartney,
Ringo Starr,
George Harrison,
Pete Best,
Tommy Moore
因此，输出现在看起来像:
So, the output will now look like :
1) A, B, C, 'Lennon : ', D
2) A, B, C, 'paul McCartney : ', E
3) A, B, C, 'Ringo Starr : ', F
4) A, B, C, 'George Harrison : ', G
5) A, B, C, 'Pete Best : ', H
6) A, B, C, 'Tommy Moore : ', I
我希望您能想到第4列的值可以是任何字符串，而不是第5列的派生词.
I hope you get the idea that the values for the 4th column can be any string, not a derivative of the 5th column.
UPDATE2:
假设出于说明目的，我的复杂查询可以简化为经典的Oracle表
UPDATE2:
Suppose, my complex query, for illustration purpose, can be simplified to the classic Oracle Tables
假设，我在那些Emp和Dept表上运行此SQL:
Suppose, I run this SQL on those Emp and Dept tables :
select emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal , dept.deptno, dept.dname, dept.loc from emp, dept where emp.deptno = dept.deptno;
在我的情况下，第4列将是"mgr"，"hiredate"，"sal"，"deptno"，"dname"和"loc".
In my case, column 4 will be "mgr", "hiredate", "sal", "deptno", "dname" and "loc".
例如，对于上述查询的以下(原始)结果行:
empno，ename，job，mgr，hiratedate，sal，deptno，dname，loc
7698，BLAKE，MANAGER，7839，1981-05-01，2850.00，30，SALES，CHICAGO
So, for example, for the following (original) result Row from the above query :
empno, ename, job, mgr, hiredate, sal , deptno, dname, loc
7698, BLAKE, MANAGER, 7839, 1981-05-01, 2850.00, 30, SALES, CHICAGO
新 6行将是
7698刹车管理器mgr 7839
7698 BLAKE MANAGER聘用日期1981-05-01
7698 BLAKE MANAGER工资2850.00
7698 BLAKE MANAGER deptno 30
7698 BLAKE MANAGER dname销售
7698 BLAKE MANAGER loc芝加哥
The new 6 rows would be
7698 BLAKE MANAGER mgr 7839
7698 BLAKE MANAGER hiredate 1981-05-01
7698 BLAKE MANAGER sal 2850.00
7698 BLAKE MANAGER deptno 30
7698 BLAKE MANAGER dname SALES
7698 BLAKE MANAGER loc CHICAGO
我应该怎么做才能转换上面的SQL以获得上面的 new 6行?
What should I do to convert the above SQL to get the above new 6 Rows ?
推荐答案
您可以使用unpivot子句.我认为这段代码将为您提供帮助:
You can use unpivot clause. I think this code will help you:
select a,b,c,'Name for ' || name_code || ' : '|| name_code as value from
(select 'A' a ,'B' b ,'C' c ,'D' d,'E' e,'F' f,'G' g,'H' h,'I' i from dual)
unpivot include nulls
(
name_for for name_code in (d as 'D', e as 'E' ,f as 'F',g as 'G',h as 'H',i as 'I')
);
问题更新后.答案更改为:
After the update of the question. The answer is changed to this:
select A,B,C,'Name for ' || name_for as value, name_code from
(select 1 A,2 B,3 C,'Lennon' D,'Paul McCartney' E, 'Ringo Starr' F,
null G, 'Pete Best'H, 'Tommy Moore' I from dual )
unpivot include nulls
(
name_for for name_code in (d,e,f,g,h,i)
)
这篇关于Oracle 11 SQL:将1行拆分为x行并插入新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
