CODEWITHSUNDEEP
mysql
jrod2020
jrod2020

Reputation: 41

MySQL Return multiple rows if aggregated value is more than 1

Table has aggregated values but i need to return multiple rows if the value is greater than one.

Here is how the table looks now:

date  description   amount

1/1/2015   alpha    3

1/1/2015    beta    1

Here is how i need it to return:

date    description amount

1/1/2015    alpha   1

1/1/2015    alpha   1

1/1/2015    alpha   1

1/1/2015    beta    1

Any help would be greatly appreciated.

Upvotes: 0

Views: 85

Answers (2)

jrod2020
jrod2020

Reputation: 41

This worked perfectly.

select t.date, t.description, 1 as amount from table t join (select @rn := @rn + 1 as n from table cross join (select @rn := 0) vars ) n on n.n <= t.amount;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You need a table of numbers. Something like this works for up to 3 and can be easily extended:

select t.date, t.description, 1 as amount
from table t join
     (select 1 as n union all select 2 union all select 3) n
     on n.n <= t.amount;

EDIT:

If you have enough rows in the table for the larger amounts, you can do:

select t.date, t.description, 1 as amount
from table t join
     (select @rn := @rn + 1 as n
      from table cross join (select @rn := 0) vars
     ) n
     on n.n <= t.amount;

Upvotes: 1

Related Questions