How to repeat a row N times based on a value within the row-CodePudding

I have a csv file containing three columns, class,malecount and femalecount as an input table.

My output should contain two columns named Class and Gender. The malecount and femalecount values indicates how many times a row should be repeated. i.e. for Class = A and malecount=2, the row (A,M) should appear twice, and for Class = C and femalecount=3, the row (C,F) should appear three times. Check the following image to see the full output.

enter image description here

DDL & DML for the table:

create table mytable (class text, malecount int, femalecount int);

insert into mytable (class, malecount, femalecount) values
( 'A',2,1),
('B',3,1),
('C',0,3),
('D',2,4);

CodePudding user response：

I used the LPAD function and then trimmed off the last comma from there I had a comma delimited string like M,M,M and F,F

then I used json_table to extract the M,M,M into three rows and the F,F into two rows etc.

here is the fiddle https://www.db-fiddle.com/f/jEXes6AttKvc9GKx1mKY2/1

Schema (MySQL v8.0)

create table mytable (class text, malecount int, femalecount int);

insert into mytable (class, malecount, femalecount) values
( 'A',2,1),
('B',3,1),
('C',0,3),
('D',2,4);

Query #1

with t as (select class, 
LPAD(' ', malecount * 2   1, 'M,') malecount,
LPAD(' ', femalecount * 2   1, 'F,') femalecount
from mytable),
t2 as(
select class,
LEFT(malecount,length(malecount)-1) malecount,
LEFT(femalecount,length(malecount)-1) femalecount
from t)
select t2.class, j.name
from t2
join json_table(
  replace(json_array(t2.malecount), ',', '","'),
  '$[*]' columns (name varchar(50) path '$')
) j where j.name = 'M'
union all
select t2.class, k.name
from t2
join json_table(
  replace(json_array(t2.femalecount), ',', '","'),
  '$[*]' columns (name varchar(50) path '$')
) k where k.name = 'F';

class	name
A	M
A	M
B	M
B	M
B	M
D	M
D	M
A	F
B	F
D	F
D	F

View on DB Fiddle

CodePudding user response：

You can use Recursive CTE as the following:

with recursive cte as
(
  select *,0 as repeats  from
  (select class, malecount as cnt, 'M' as Gender from Tbl
    union
    select class, femalecount as cnt, 'F' as Gender from Tbl
   ) D
  union all
  select class,cnt,Gender, repeats 1 from cte
  where repeats<cnt-1
  )
  select class, gender from cte 
  where cnt>0
  order by gender desc,class;

See a demo from db-fiddle.

The initial query of the recursive CTE rearranged the input table to be like the following:

Class	Count	Gender
A	2	M
B	3	M
C	0	M
D	2	M
A	1	F
B	1	F
C	3	F
D	4	F

Then the recursive query starts repeating the rows until the condition repeats<cnt-1 is met.

CodePudding user response：

WITH RECURSIVE
  -- define maximal amount of rows per class per gender to be generated
cte1 AS ( SELECT MAX(GREATEST(malecount, femalecount)) max_count
          FROM test),
  -- generate natural numbers till max. amount found above
cte2 AS ( SELECT 1 num
          UNION ALL
          SELECT num 1 
          FROM cte1
          CROSS JOIN cte2
          WHERE cte2.num <= cte1.max_count)
  -- generate rows for male
SELECT test.class, 'm' gender
FROM test
JOIN cte2 ON cte2.num <= test.malecount
UNION ALL
  -- generate rows for female
SELECT test.class, 'f'
FROM test
JOIN cte2 ON cte2.num <= test.femalecount
  -- final sorting
ORDER BY gender DESC, class

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=694dbb214e4c0cd5524800c56a02dc65

CodePudding user response：

You can use the function,the expression:

CASE input_expression WHEN when_expression THEN result_expression [...n ] [ ELSE else_result_expression