CODEWITHSUNDEEP
mysqlsql
BeJotEl
BeJotEl

Reputation: 3

How to create a set of rows from a number in MySQL

I have a select statement with the following result table

name count
A    2
B    1
C    3
D    2

The numbers are a result of a count operation. What I want is:

name number
A    1
A    2
B    1
C    1
C    2
C    3
D    1
D    2

The result table should have the numbers from 1 to the count number. How can this be done with SQL(-function) or temporary table or any other way?

Upvotes: 0

Views: 87

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You need to get an enumerated list of integers. If your table is big enough, you can use that:

select t.name, n.n
from (select @rn := @rn + 1 as n
      from t cross join
           (select @rn := 0) vars
     ) n join
     t
     on n.n <= t.count;

Upvotes: 1

Related Questions