我问了 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行并插入新列
发布评论