how generate a series of numbers by grouping the data in db2 or sql

January 3, 2022

I have data as below

Manager	City	Building	Floor	Number of Seats
xxx	BLR	SA2	2F	2
xyz	BLR	SA2	2F	3
xya	BLR	SA2	3F	2
xjk	BLR	SA2	3F	1

Resulting data should be as below, grouping by manager or building and floor, need to generate series of number based on the number of seats.

Manager	City	Building	Floor	Number of Seats
xxx	BLR	SA2	2F	1
xxx	BLR	SA2	2F	2
xyz	BLR	SA2	2F	3
xyz	BLR	SA2	2F	4
xyz	BLR	SA2	2F	5
xya	BLR	SA2	3F	1
xya	BLR	SA2	3F	2
xjk	BLR	SA2	3F	3

>Solution :

You would carterisan product by the no_of_seats and then order it by manager id.

Here is a way to do this

 with row_gen(rn) as (
        select 1 from SYSIBM.SYSDUMMY1    
        union all
        select rn+ 1 from dummy where id < 10000 /*assumption that the number of seats isnt more than 10000*/
    )
    ,data
    as (
    select tb.mgr,tb.city,tb.bldg,tb.floor,rg.rn
      from row_gen rg
      join table tb
        on tb.no_of_seats<=rg.rn
        )
select d.mgr,d.city,d.bldg,d.floor
      ,row_number() over(partition by d.city,d.bldg,d.floor order by d.mgr) as no_of_seats
  from data d