How to multiply rows in oracle query by count column?

Question

I have example table:

OVERALL_COUNT | NAME | OTHER_ATTRIBUTE

1 | ABC | q
5 | ACB | w
2 | CBA | e
........
[many rows, like thousands]

Update: the rows in overall_count column are unknown, they are calculated by other query

I need to get specific result where names and other attributes duplicates by overall_count.

How I can write query to get result like this?

ABC | q
ACB | w
ACB | w
ACB | w
ACB | w
ACB | w
CBA | e
CBA | e

Please show us the actual starting table, not a distillate of it. — Tim Biegeleisen, Jul 13 '21 at 06:26
It seems to me that to know how many of items there are at the outset, we'd need to basically be _starting_ off with the expected output. — Tim Biegeleisen, Jul 13 '21 at 06:37
Original table just has many rows(like thousands) and other columns that can't be used for a helping. There are second_names, phone_number, adresses... I thought there some function like group by but multiply — vladsrc, Jul 13 '21 at 06:40
@tim-biegeleisen The starts table is already has calculated overall_count. There is no base table where are overall calculating — vladsrc, Jul 13 '21 at 06:43

astentx · Answer 1 · 2021-07-13T07:01:12.537

For 12c and above you may use lateral join:

with a(OVERALL_COUNT, NAME, OTHER_ATTRIBUTE) as (
  select 1, 'ABC', 'q' from dual union all
  select 5, 'ACB', 'w' from dual union all
  select 2, 'CBA', 'e' from dual
)
select a.*
from a
  cross join lateral (
    select 1
    from dual
    connect by level <= a.overall_count
  )
order by name

OVERALL_COUNT | NAME | OTHER_ATTRIBUTE
------------: | :--- | :--------------
            1 | ABC  | q              
            5 | ACB  | w              
            5 | ACB  | w              
            5 | ACB  | w              
            5 | ACB  | w              
            5 | ACB  | w              
            2 | CBA  | e              
            2 | CBA  | e

Or for 11 version:

with b(
  overall_count
  , name
  , other_attribute
  , l
) as (
  select a.*, 1
  from a

  union all
  
  select
    overall_count
    , name
    , other_attribute
    , l + 1
  from b
  where l < overall_count
)
select *
from b
order by name

OVERALL_COUNT | NAME | OTHER_ATTRIBUTE |  L
------------: | :--- | :-------------- | -:
            1 | ABC  | q               |  1
            5 | ACB  | w               |  1
            5 | ACB  | w               |  2
            5 | ACB  | w               |  4
            5 | ACB  | w               |  5
            5 | ACB  | w               |  3
            2 | CBA  | e               |  2
            2 | CBA  | e               |  1

db<>fiddle here

Note that `cross apply` is non-standard SQL. If you want to use a lateral join with standard ANSI SQL use `cross join lateral` instead which Oracle also supports. — , Jul 13 '21 at 06:24
@astentx Thanks for reply. I think I must update question. There is some correction: the rows in count column are unknown count. Thinking about writing stored procedure based on this answer. But if you know some simplier way it will be great. — vladsrc, Jul 13 '21 at 06:29
@vladsrc There's no any predefined constants in this queries. They will generate as much rows as there is in the `OVARALL_COUNT` as exactly was asked in the question — astentx, Jul 13 '21 at 06:56
@a_horse_with_no_name Thank you. As `connect by` is Oracle specific, it makes not much value, but I've edited the answer to conform standard syntax of this part — astentx, Jul 13 '21 at 07:02

score 0 · Answer 2 · answered Jul 13 '21 at 06:14

0

You could use a join approach with the help of a sequence table:

WITH seq AS (
    SELECT 1 AS cnt FROM dual UNION ALL
    SELECT 2 FROM dual UNION ALL
    SELECT 3 FROM dual UNION ALL
    SELECT 4 FROM dual UNION ALL
    SELECT 5 FROM dual
)

SELECT t1.NAME, t1.OTHER_ATTRIBUTE
FROM yourTable t1
INNER JOIN seq t2
    ON t2.cnt <= t1.OVERALL_COUNT
ORDER BY t1.NAME;

Demo

Note that I hard-coded a trivial sequence table with only 5 values (which happens to cover all your sample data). Check this SO question and answers for some more ideas on how to work with sequence tables in Oracle.

answered Jul 13 '21 at 06:14

Tim Biegeleisen

502,043
27
286
360

Thanks for answer. this is a nice trick, but I doesn't know how many rows in overall_count can be. Its changing dynamically. Any other options please? – vladsrc Jul 13 '21 at 06:19
Then you need to show us the original table. That table you _did_ show us indeed has the overall count in it. – Tim Biegeleisen Jul 13 '21 at 06:25
original table just have many rows, like thousands... But I working with a small part of them. Small part of them with unkbown count. Looking for your answer I thinking about stored procedures. Maybe it will work for me. I thought there is some simplier option, for example: select name, other_attributes from table multiplied by overall_count – vladsrc Jul 13 '21 at 06:36

How to multiply rows in oracle query by count column?

2 Answers2

Demo