ainhoa sayans ainhoa sayans - 15 days ago 7
SQL Question

how to duplicate my sql results?

I want to get multiple results from one line in table based on a number of that table.

ID | count
A | 12
B | 138


So I want the output of the query to be 12 times A (no matter if with a counter or not) and 138 times B.
I have seen a solution by adding a count table on the DB, but I can't do that.

MT0 MT0
Answer
SELECT t.id, r.COLUMN_VALUE, t.cnt
FROM   table_name t,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= t.cnt
           )
           AS SYS.ODCINUMBERLIST
         )
       ) r;