|
|
Re: Multiple rows based on column [message #662370 is a reply to message #662369] |
Thu, 27 April 2017 04:02 |
|
sss111ind
Messages: 636 Registered: April 2012 Location: India
|
Senior Member |
|
|
Thanks Michel Sir, but it is not forming, is there any other way.
With data as (select DEPTNO, ','||noauth||',' noauth from DEPT1)
select DEPTNO, column_value value_nb,
substr(noauth,
instr(noauth, ',', 1, column_value)+1,
instr(noauth, ',', 1, column_value+1)-instr(noauth, ',', 1, column_value)-1
) noauth
from data,
table(cast(multiset(select level from dual
connect by level < length(noauth)-length(replace(noauth,',')))
AS sys.odciNumberList))
ORDER BY 1, 2;
I found 1 query
WITH temp AS (SELECT LEVEL rn FROM dual CONNECT BY LEVEL<=100)
SELECT * FROM (SELECT * FROM dept1 ,temp ORDER BY deptno ,rn)
where noauth>=rn;
Regards,
Nathan
[Updated on: Thu, 27 April 2017 04:20] Report message to a moderator
|
|
|
|
|
|
Re: Multiple rows based on column [message #662391 is a reply to message #662368] |
Fri, 28 April 2017 17:31 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
-- test data that you provided:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM dept1
2 /
DEPTNO DNAME LOC NOAUTH
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 1
20 RESEARCH DALLAS 2
30 SALES CHICAGO 3
40 OPERATIONS BOSTON 4
4 rows selected.
-- solution for your version:
SCOTT@orcl_12.1.0.2.0> SELECT d.*, t.*
2 FROM dept1 d,
3 TABLE
4 (CAST
5 (MULTISET
6 (SELECT ROWNUM
7 FROM DUAL
8 CONNECT BY LEVEL <= d.noauth)
9 AS SYS.ODCINUMBERLIST)) t
10 /
DEPTNO DNAME LOC NOAUTH COLUMN_VALUE
---------- -------------- ------------- ---------- ------------
10 ACCOUNTING NEW YORK 1 1
20 RESEARCH DALLAS 2 1
20 RESEARCH DALLAS 2 2
30 SALES CHICAGO 3 1
30 SALES CHICAGO 3 2
30 SALES CHICAGO 3 3
40 OPERATIONS BOSTON 4 1
40 OPERATIONS BOSTON 4 2
40 OPERATIONS BOSTON 4 3
40 OPERATIONS BOSTON 4 4
10 rows selected.
-- simpler solution for after you upgrade to 12c someday, using lateral as suggested by Solomon:
SCOTT@orcl_12.1.0.2.0> SELECT d.*, t.*
2 FROM dept1 d,
3 LATERAL
4 (SELECT ROWNUM AS rn
5 FROM DUAL
6 CONNECT BY LEVEL <= d.noauth) t
7 /
DEPTNO DNAME LOC NOAUTH RN
---------- -------------- ------------- ---------- ----------
10 ACCOUNTING NEW YORK 1 1
20 RESEARCH DALLAS 2 1
20 RESEARCH DALLAS 2 2
30 SALES CHICAGO 3 1
30 SALES CHICAGO 3 2
30 SALES CHICAGO 3 3
40 OPERATIONS BOSTON 4 1
40 OPERATIONS BOSTON 4 2
40 OPERATIONS BOSTON 4 3
40 OPERATIONS BOSTON 4 4
10 rows selected.
[Updated on: Fri, 28 April 2017 17:32] Report message to a moderator
|
|
|
|
|
|