I have data as below

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.

CodePudding user response：

The solution below doesn't depend on maximum number of seats.

WITH 
/*
  MYTAB (Manager, City, Building, Floor, Number_of_Seats) AS
(
VALUES
  ('xxx', 'BLR', 'SA2', '2F', 2)
, ('xyz', 'BLR', 'SA2', '2F', 3)
, ('xya', 'BLR', 'SA2', '3F', 2)
, ('xjk', 'BLR', 'SA2', '3F', 1)
)
, 
*/
  T (Manager, City, Building, Floor, Number_of_Seats) AS 
(  
SELECT Manager, City, Building, Floor, Number_of_Seats 
FROM MYTAB
    UNION ALL
SELECT Manager, City, Building, Floor, Number_of_Seats - 1
FROM T
WHERE Number_of_Seats > 1
)
SELECT 
  Manager, City, Building, Floor
, ROW_NUMBER () OVER (PARTITION BY City, Building, Floor ORDER BY Manager) AS Number_of_Seats
FROM T
ORDER BY City, Building, Floor, Manager

If you uncomment the commended out block and run the statement as is, you get the following output.

CodePudding user response：

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

CodePudding user response：

with row_gen(rn) as (
              
  SELECT 1 AS rn FROM SYSIBM.SYSDUMMY1 
    UNION ALL
    SELECT rn 1  FROM row_gen WHERE rn  1 <=50
    ) 
    ,data
    as (
    select tb.manager,tb.location,tb.building,tb.floor,rg.rn
      from row_gen rg
      join db2inst1.tmp_cio_data tb
        on  rg.rn <= tb.seat_range_to
        )
select d.manager,d.location,d.building,d.floor,
     row_number() over(partition by d.location,d.building,d.floor order by d.manager) as no_of_seats
  from data d WHERE d.floor = 11 AND d.building = '12D'