CODEWITHSUNDEEP
sqloracle-databaseiterationcalculated-columnsrepeat
6nagi9
6nagi9

Reputation: 544

Select repeated data based on column value

Suppose I have a dataset as

----------------------
 col1 | col2 | col3 |
----------------------
   a      b      3
   c      d      2
----------------------

Now Is there a way in SQL to select 

----------------------
 colx | coly | colz |
----------------------
   a      b      1
   a      b      2
   a      b      3
   c      d      1
   c      d      2
----------------------

i.e. col1 and col2 repeated col3 number of times.

Upvotes: 0

Views: 90

Answers (3)

Noel
Noel

Reputation: 10525

If you are using 11gR2, you can use recursive CTE.

with cte(x,y,z) as (
  select col1, col2, col3   --select all the records from the table
    from table_name

  union all

  select x, y, z-1          --select previously selected rows and decrement the col3 by 1
    from cte                --until the col3 becomes 1
   where z > 1
  )
select * 
  from cte
 order by x,y,z;

Upvotes: 0

Sebas
Sebas

Reputation: 21522

SELECT DISTINCT t.col1 AS colx, t.col2 AS coly, level AS colz
FROM tablee t
CONNECT BY level <= t.col3
ORDER BY t.col1, t.col2, level

fiddle: http://sqlfiddle.com/#!4/01f5b/12

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Please try:

with T (colx , coly , colz , mxcol3) as
(
  select col1, col2, 1 col3, max(col3) over (partition by col1, col2)  mxcol3 
  from YourTab 

  union all

  select colx , coly , colz +1 colz , mxcol3 
  from T 
  where colz +1<=mxcol3
)
select
  colx, coly, colz
From T
order by colx, coly, colz;

Upvotes: 0

Related Questions