CODEWITHSUNDEEP
sqloracle-database
Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Repeat each value n times as rows in SQL

I have been trying to achieve this in SQL (Oracle 11g) for a while but could not find a proper way to do it.

My table names has the following rows:

NAME      REPEAT
----      ------
KAUSHIK   2
KARTHIK   3
NIDHI     1
ASHWINI   5
JAGADEESH 6

What I need is an output like this:

NAME
----
KAUSHIK    -- 2 rows
KAUSHIK
KARTHIK
KARTHIK    -- 3 rows
KARTHIK
NIDHI      -- 1 row
ASHWINI
ASHWINI    -- 5 rows
...

and so on.

One of the queries which I have tried so far, which of course is not working. I tried to use unpivot as well but could not seem to find a proper way to accomplish this.

SELECT m.name
FROM names m
INNER JOIN
  ( SELECT name, repeat FROM names
  ) n
ON m.name = n.name
  CONNECT BY LEVEL <= n.repeat;

Upvotes: 0

Views: 6713

Answers (4)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

I just found an alternative with connect by and correlated sub-query.

select name
from   names o
connect by level <= ( select repeat from names 
                        i where i.name = o.name )
AND                        
prior name = name and
           prior sys_guid() is not null
           order by name;

Upvotes: 1

xQbert
xQbert

Reputation: 35323

If we presume that your all_objects system object has more objects than the max repeat...

SELECT n.name
FROM names n
LEFT JOIN (Select rownum from all_objects) z
 on z.rowNum < n.repeat

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

Try this:

select * from names
cross join (select rownum n from dual
            connect by level <= (select max(repeat) from names))
where n <= repeat
order by name

Upvotes: 3

Radim Bača
Radim Bača

Reputation: 10701

You may use some temp table containing list of numbers 1 to N, where N is the highest number in your table names. Let call it num(o int) Then the query will be

SELECT *
FROM names, num
WHERE num.o <= names.repeat

Upvotes: 1

Related Questions