Home » SQL & PL/SQL » SQL & PL/SQL » Multiple rows based on column (Oracle 10g)
Multiple rows based on column [message #662368] Thu, 27 April 2017 02:53 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Hi All,

How can I generate multiple rows based on column noauth. For example the noauth for deptno 20 is 2 so 2 rows need to be generated .
for deptno 10 1 rows ,deptno 20 2 rows,deptno 30 3 rows ,deptno 40 4 rows. And total of 10 rows is the output required.
Please suggest whether we required pl/sql or sql code enough to do it.



select * from dept;

create table dept1 as select * from dept;

ALTER TABLE dept1 ADD noauth NUMBER;

UPDATE dept1 SET noauth=ROWNUM ;

commit;

select * from dept1;

output
---------------
10	ACCOUNTING	NEW YORK	1
20	RESEARCH	DALLAS	2
20	RESEARCH	DALLAS	2
30	SALES	CHICAGO	3
30	SALES	CHICAGO	3
30	SALES	CHICAGO	3
40	OPERATIONS	BOSTON	4
40	OPERATIONS	BOSTON	4
40	OPERATIONS	BOSTON	4
40	OPERATIONS	BOSTON	4




Regards,
Nathan
Re: Multiple rows based on column [message #662369 is a reply to message #662368] Thu, 27 April 2017 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/95011/569066/102589/#msg_569066

Re: Multiple rows based on column [message #662370 is a reply to message #662369] Thu, 27 April 2017 04:02 Go to previous messageGo to next message
sss111ind
Messages: 634
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 #662371 is a reply to message #662370] Thu, 27 April 2017 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but it is not forming

Yes it is.
Show us why it is not.


Quote:
I found 1 query

And you found it is not working.

Re: Multiple rows based on column [message #662375 is a reply to message #662368] Thu, 27 April 2017 07:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
With over 500 messages you should know by now to post oracle version. Look at LATERAL, if you are on 12C.

SY.
Re: Multiple rows based on column [message #662376 is a reply to message #662375] Thu, 27 April 2017 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version is in the title.
Re: Multiple rows based on column [message #662391 is a reply to message #662368] Fri, 28 April 2017 17:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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

Re: Multiple rows based on column [message #662392 is a reply to message #662391] Sat, 29 April 2017 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Hopefully you are there to spoonfeed the lazy and do his homework.

Re: Multiple rows based on column [message #663110 is a reply to message #662392] Mon, 22 May 2017 08:37 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you madam for your solution it's working fine.

Regards,
Nathan
Re: Multiple rows based on column [message #663113 is a reply to message #663110] Mon, 22 May 2017 10:51 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So it is.

Previous Topic: Oracle Sequences
Next Topic: Improve Spool Performance
Goto Forum:
  


Current Time: Thu Mar 28 12:45:16 CDT 2024