CODEWITHSUNDEEP
mysqlsql
theglossy1
theglossy1

Reputation: 553

Making one row many rows

I have some rows like this:

 school      4
 hotel       2
 restaurant  6

I would like to do have this output like this:

 school 1
 school 2
 school 3
 school 4
 hotel 1
 hotel 2
 restaurant 1
 ...
 restaurant 6

Is there a MySQL query I can run to output it like this (i.e., the number of rows output corresponds to the number in the second field)?

Upvotes: 0

Views: 77

Answers (2)

ntalbs
ntalbs

Reputation: 29458

In Oracle or PostgreSQL, this kind problem can be solved relatively simple with row generator. Unfortunately, MySQL doesn't have any simple way to generate rows, you should have to do it manually. You have to create a view.

create or replace view generator_16 as
  select 0 n union all select 1  union all select 2  union all
  select 3   union all select 4  union all select 5  union all
  select 6   union all select 7  union all select 8  union all
  select 9   union all select 10 union all select 11 union all
  select 12  union all select 13 union all select 14 union all
  select 15;

This view has one column n and 16 rows from 0 to 15. With this view, you can write a query like this:

select a.name, b.n 
from t a, generator_16 b 
where b.n <= a.n and b.n > 0;

Since the rows of view starts from 0, there's additional filter condition b.n > 0 to exclude 0. If you define a view which starts from 1, the additional condition would not necessary.

I assume that the number is small (below 16). If you have to deal the case that the number over 15, then you should define an another view which contains 256 rows:

create or replace view generator_256 as
  select (hi.n*16 + lo.n) as n
  from generator_16 hi, generator_16 lo;

Then update the query to use generator_256.

For more detailed explanation for generator_16, generator_256 views, reading MySQL Row Generator would help you.

Upvotes: 1

theglossy1
theglossy1

Reputation: 553

Thanks for JB Nizet for telling me that MySQL would not be able to do this easily on it's own. It inspired me to write this PHP script:

$result = mysqli_query($dbc,"select abbrev,chapters from books");
while ($output = mysqli_fetch_array($result,MYSQL_ASSOC)) {
 for ($i=1; $i<=$output['chapters']; $i++) {
  echo "{$output['abbrev']}$i\n";
 }
}

Upvotes: 1

Related Questions