Sql query without using loops

Question

I have one database table test.The structure of the table is:

Col1(varchar)  Col2(number)

The table has 2 rows:

Abc 5
Def 6

I desire the output to be:

Col1
Abc
Abc
Abc
Abc
Abc
Def
Def
Def
Def
Def
Def

I need to write a single query without using plsql loops to acheive this output.Please help.

Can't you just use SELECT col1, ABC, ABC, ABC, ABC, ABC, def, def, def, def, def? — Breeze
– Breeze, Commented May 6, 2016 at 21:58
Is it correct that you do not want to use plsql because you want the query to work with more than just Postgresql/Oracle? — musically_ut
– musically_ut, Commented May 6, 2016 at 21:58
Which RDBMS? Please tag accordingly. (This can be done with some variation on a nums table, but even that varies across systems.) — Jeroen Mostert
– Jeroen Mostert, Commented May 6, 2016 at 21:59
I actually work in Tableau reporting tool.we use Oracle 11g darabase.Tableau dont support Plsql. — Priyanka Das
– Priyanka Das, Commented May 6, 2016 at 22:01

trincot · Accepted Answer · 2016-05-06 22:17:48Z

2

Here is one way to do it:

select     col1
from       mytable,
           (select     rownum r
            from       dual
            connect by rownum <= (select max(col2) from mytable))
where      r <= col2

answered May 6, 2016 at 22:17

trincot

357k38 gold badges282 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2016-05-06 22:22:31Z

Oracle Setup:

CREATE TABLE table_name (col1, col2 ) AS
SELECT 'ABC', 5 FROM DUAL UNION ALL
SELECT 'DEF', 6 FROM DUAL UNION ALL
SELECT 'GHI', 0 FROM DUAL;

Query - Using a Collection:

SELECT t.Col1
FROM   table_name t,
       TABLE(
         CAST(
           MULTISET(
             SELECT 1
             FROM   DUAL
             WHERE t.Col2 > 0
             CONNECT BY LEVEL <= t.Col2
           )
           AS SYS.ODCINUMBERLIST
         )
       );

Query - Using a recursive sub-query factoring clause:

WITH sqfc ( COL1, COL2 ) AS (
  SELECT col1, col2 FROM table_name
    UNION ALL
  SELECT col1, col2 - 1 FROM sqfc WHERE col2 > 1
)
SELECT col1
FROM   sqfc
WHERE  col2 > 0 -- Needed if col2 is zero
ORDER BY Col1;

Output (from both queries):

COL1
----
ABC  
ABC  
ABC  
ABC  
ABC  
DEF  
DEF  
DEF  
DEF  
DEF  
DEF

Collectives™ on Stack Overflow

Sql query without using loops

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related