show additional rows based on field value (Oracle SQL)

user_16478967 0
Viewed 40

I have a query which shows the result as follows

NAME   RATIO
====   =====
S        3
M        2

I want to make Ratio Groups (A,B,C .....) based on Names For Example, there are 3 ratios for Name S so A,B,C Group will be made for S and D,E will be made for M. The result I want to be displayed will be like this

NAME      GROUP
====      =====
S           A
S           B
S           C
M           D
M           E

To pass Letter dynamically, I tried using the the ANSI Characater CHR(65) for A and so on.. but couldn't find a way to write the query.

How can I achieve this using SQL Query?

Regards

oracle sql
1 Answers

You can use recursive subquery factoring to generate the extra rows:

with rcte (name, ratio, lvl) as (
  select name, ratio, 1
  from your_table
  union all
  select name, ratio, lvl + 1
  from rcte
  where lvl < ratio
)
select name, ratio
from rcte
-- order by 'name desc' to get same order shown in question
order by name desc, ratio;

NAME RATIO
---- -----
S        3
S        3
S        3
M        2
M        2

And then get the row number for each of those, and add that to chr(64) to get the corresponding letter. As long as you don't have too many groups, of course...

with rcte (name, ratio, lvl) as (
  select name, ratio, 1
  from your_table
  union all
  select name, ratio, lvl + 1
  from rcte
  where lvl < ratio
)
select name, ratio,
  -- order by 'name desc' to get same order shown in question
  chr(64 + row_number() over (order by name desc, ratio, lvl)) as grp
from rcte
order by grp

NAME RATIO GRP
---- ----- ---
S        3 A
S        3 B
S        3 C
M        2 D
M        2 E

db<>fiddle

It isn't obvious why the 'S' rows come before and have groups starting first, before the 'M' ones. Do get your expected result I've just based the ordering on the reverse-alphabetic sort of the name values - if you have something more sensible (like another column you haven't shown) then change the order by clause to use that instead.

user_2663040
Related