CODEWITHSUNDEEP
sqloracle-databaseoracle11g
K.I
K.I

Reputation: 588

Query that multiplies row by a number in a row from table in Oracle Database

I have a probably weird question, but I thought I will ask before I develop a work around. I have a table in a database which contains "product" data including its quantity like so:

NAME__|__SIZE______| QTY__
ITEM1 | LARGE      | 30
ITEM2 | MEDIUM     |  1
ITEM3 | SMALL      |  1
ITEM4 | EXTRA SMALL|  1
ITEM5 | 116        |  1

I have built a query that returns all this data, however I need to return one row per item, so in this example my query should return 34 lines of data, 30 of which will have size large.

Desired query should return:

NAME__|__SIZE______| 
ITEM1 | LARGE      | 
ITEM1 | LARGE      | 
ITEM1 | LARGE      | 
ITEM1 | LARGE      | 
...total 30 times...

ITEM2 | MEDIUM     |  
ITEM3 | SMALL      |  
ITEM4 | EXTRA SMALL|  
ITEM5 | 116        |

Is this possible?

Upvotes: 0

Views: 50

Answers (2)

MT0
MT0

Reputation: 168256

Oracle Setup:

CREATE TABLE products ( NAME, SZ, QTY ) AS
SELECT 'ITEM1', 'LARGE',       10 FROM DUAL UNION ALL
SELECT 'ITEM2', 'MEDIUM',       1 FROM DUAL UNION ALL
SELECT 'ITEM3', 'SMALL',        2 FROM DUAL UNION ALL
SELECT 'ITEM4', 'EXTRA SMALL',  1 FROM DUAL UNION ALL
SELECT 'ITEM5', '116',          1 FROM DUAL;

Query 1 - Use a hierarchical query:

SELECT p.*
FROM   products p,
       TABLE( CAST( MULTISET ( SELECT 1 FROM DUAL CONNECT BY LEVEL <= p.qty )
                    AS SYS.ODCINUMBERLIST ) );

Query 2 - Use a recursive sub-query factoring clause:

WITH rsqfc ( name, sz, qty, n ) AS (
  SELECT p.*, qty FROM products p
UNION ALL
  SELECT name, sz, qty, n-1 FROM rsqfc WHERE n > 1
)
SELECT name, sz, qty
FROM   rsqfc
ORDER BY NAME;

Output:

NAME  SZ                 QTY
----- ----------- ----------
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM1 LARGE               10 
ITEM2 MEDIUM               1 
ITEM3 SMALL                2 
ITEM3 SMALL                2 
ITEM4 EXTRA SMALL          1 
ITEM5 116                  1

Upvotes: 1

Glenn
Glenn

Reputation: 9170

I guess you could use a CTE. Something along these lines (example run on Postgresql):

CREATE TABLE product(name text, size text, qty int);
insert into product values('ITEM1', 'LARGE', 5);
insert into product values('ITEM2', 'MEDIUM', 2);

WITH RECURSIVE temp(rn, name, size, qty) AS (
    SELECT 1, name, size, qty
      FROM product
    UNION ALL
    SELECT rn + 1, name, size, qty
      FROM temp
      WHERE rn < qty
)
SELECT name, size
  FROM temp
  ORDER BY name, size

And the output:

name size
---- ----
ITEM1 LARGE
ITEM1 LARGE
ITEM1 LARGE
ITEM1 LARGE
ITEM1 LARGE
ITEM2 MEDIUM
ITEM2 MEDIUM

Upvotes: 1

Related Questions