CODEWITHSUNDEEP
sqloracle-databaserow-number
Joe Jiang
Joe Jiang

Reputation: 33

How to select a number of rows according to a column

So I have got two columns in an Oracle database:

Name / count

I would like to print the name x times, x being the count.

E.g. Paul / 5 would mean Paul being printed 5 times.
     Sam / 6 would mean Sam being printed 6 times

Tried row_number over but not sure how it works?

Upvotes: 2

Views: 70

Answers (5)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use a connect by level <= some_number logic containing cross join to link with your table tab :

with tab(Name,"count") as
( select 'Paul', 5 from dual union all select 'Sam', 6 from dual )
 select name, level as seq 
   from dual d
  cross join tab t
connect by level <= t."count"
    and prior name = name
    and prior sys_guid() is not null;

Demo

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143083

Yet another option (your query starts at line #4):

SQL> with your_table as
  2    (select 'paul' as name, 5 as count from dual union all
  3     select 'sam'  as name, 6 as count from dual)
  4  select name
  5  from your_table cross join table (cast (multiset (select level from dual
  6                                                    connect by level <= count
  7                                                   ) as sys.odcinumberlist));

NAME
----
paul
paul
paul
paul
paul
sam
sam
sam
sam
sam
sam

11 rows selected.

SQL>

Upvotes: 1

Popeye
Popeye

Reputation: 35930

You can use connect by as following:

SQL> WITH YOUR_TABLE AS
  2  (SELECT 'paul' as NAME, 5 AS COUNT FROM DUAL UNION ALL
  3  SELECT 'sam' as NAME, 6 AS COUNT FROM DUAL
  4  ) -- YOUR ACTUAL QUERY STARTS FROM LINE#5
  5  Select t.name, m.lvl
  6  from your_table t
  7  join
  8  (Select level as lvl
  9  from
 10    (Select max(count) as maxcount
 11     from your_table)
 12  Connect by level <= maxcount) m
 13  On (t.count >= m.lvl)
 14  ORDER BY 1,2;

NAME        LVL
---- ----------
paul          1
paul          2
paul          3
paul          4
paul          5
sam           1
sam           2
sam           3
sam           4
sam           5
sam           6

11 rows selected.

SQL>

Cheers!!

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

this will work:

select  name
  from  Table1,
        (select  level lvl
           from  dual
           connect by level <= (select max(cnt) from Table1 )
        )
  where lvl <= cnt
  order by name;

check fiddle:http://sqlfiddle.com/#!4/14a67/1

Thanks!!!

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13026

you need recursive query to achieve this.

with cte(nam, ctr) as (
    select 'Paul' as nam, 5 as ctr from dual
    union all
    select 'Sam', 6 as ctr from dual
),
cte2(nam, ct, ctr) as (
    select nam, 1 as ct, ctr from cte
    union all
    select nam, ct + 1, ctr from cte2
    where ct<ctr
)select nam, ct from cte2  
order by nam asc

output:

enter image description here

See sqlfiddle

Upvotes: 1

Related Questions