I have a table that looks something like the following.
| letter | count |
|---|---|
| A | 5 |
| B | 3 |
How can I do a SELECT in MySQL that would produce something like the following output?
| column1 | column2 |
|---|---|
| A | 1 |
| A | 2 |
| A | 3 |
| A | 4 |
| A | 5 |
| B | 1 |
| B | 2 |
| B | 3 |
I have not tried anything just yet because I am pretty new at MySQL and didn’t have a great idea where to start.
1
Here’s one possible solution:
<code>create table mytable (
letter char(1),
count int
);
insert into mytable values ('A', 5), ('B', 3);
select t.letter, n.n
from mytable as t
join (values row (1), row (2), row (3), row (4), row (5)) as n(n)
on t.count >= n.n
order by t.letter, n.n;
</code>
<code>create table mytable (
letter char(1),
count int
);
insert into mytable values ('A', 5), ('B', 3);
select t.letter, n.n
from mytable as t
join (values row (1), row (2), row (3), row (4), row (5)) as n(n)
on t.count >= n.n
order by t.letter, n.n;
</code>
create table mytable (
letter char(1),
count int
);
insert into mytable values ('A', 5), ('B', 3);
select t.letter, n.n
from mytable as t
join (values row (1), row (2), row (3), row (4), row (5)) as n(n)
on t.count >= n.n
order by t.letter, n.n;
Result, tested in MySQL 8.0.39:
<code>+--------+---+
| letter | n |
+--------+---+
| A | 1 |
| A | 2 |
| A | 3 |
| A | 4 |
| A | 5 |
| B | 1 |
| B | 2 |
| B | 3 |
+--------+---+
</code>
<code>+--------+---+
| letter | n |
+--------+---+
| A | 1 |
| A | 2 |
| A | 3 |
| A | 4 |
| A | 5 |
| B | 1 |
| B | 2 |
| B | 3 |
+--------+---+
</code>
+--------+---+
| letter | n |
+--------+---+
| A | 1 |
| A | 2 |
| A | 3 |
| A | 4 |
| A | 5 |
| B | 1 |
| B | 2 |
| B | 3 |
+--------+---+
The number of rows in n should be at least as much as the largest count you have in the table.
If you can’t predict the largest count, or if this solution seems beyond your current skill level in SQL, you should consider if it would be easier to solve in some other programming language.
1