Oracle splits a row into multiple lines

Source: Internet
Author: User


DROP TABLE TEST PURGE;
CREATE TABLE TEST (po_number VARCHAR2 (+), Product_Name VARCHAR2 (+), quantity number);
INSERT into TEST VALUES (' CJ201300048 ', ' tp-link network switch TP-1024DT ', 2);
INSERT into TEST VALUES (' CJ201300049 ', ' 4-way/Trenton 600-wire Hemisphere/Seagate HDD 500G ', 1);
INSERT into TEST VALUES (' CJ201300050 ', ' TP link wireless router DAP-1353 ', 2);
INSERT into TEST VALUES (' CJ201300060 ', ' ke mi electronic Access mf850d ', 1);
Commit

With a as (select ROWNUM rn from dual CONNECT by ROWNUM <= (select MAX (t.quantity) from TEST t))
SELECT t1.po_number,t1.product_name,1 quantity from TEST T1 left JOIN A on t1.quantity >= a.rn

Sql> with A as (select ROWNUM rn from dual CONNECT by ROWNUM <= (select MAX (t.quantity) from TEST t))
2 SELECT t1.po_number,t1.product_name,1 quantity from TEST T1 left JOIN A on t1.quantity >= a.rn
3;

Po_number Product_Name QUANTITY
---------------- ---------------------------------------------------------------- ----------
CJ201300050 TP Link Wireless Router DAP-1353 1
CJ201300050 TP Link Wireless Router DAP-1353 1
CJ201300048 tp-link Network Switch TP-1024DT 1
CJ201300048 tp-link Network Switch TP-1024DT 1
CJ201300060 Electronic access Control mf850d 1
CJ201300049 4-Way/Trenton 600-wire Dome/Seagate hard Drive 500G 1

6 Rows selected

Sql>

Oracle splits a row into multiple lines

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.