CODEWITHSUNDEEP
mysqlsqlunpivot
mcmurphy
mcmurphy

Reputation: 805

Mysql return one row per count

Let's say I have a table like this:

CREATE TABLE `car2` (
    `id` INT(11) NOT NULL,
    `car` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `count` INT(3) UNSIGNED NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

With this data:

ID    CAR            COUNT
1     Fusion         2
2     Tesla Model 3  0
3     Honda CRV      1
4     Toyota Camry   3

Is there a query to return one row for each count? It should return no rows if the count is zero.

It should return something like this:

CAR           ID
Fusion        1
Fusion        1
Honda CRV     3
Toyota Camry  4
Toyota Camry  4
Toyota Camry  4

So far I've tried various combinations of self and CROSS JOINS. None have worked.

Upvotes: 0

Views: 185

Answers (1)

peterm
peterm

Reputation: 92795

If id values in the table are monotonically increasing starting with 1 and the max count value is less than the total number of rows then you can simply do

SELECT s.car, s.id
  FROM car2 s JOIN car2 t
    ON t.id BETWEEN 1 AND s.count
 ORDER BY car

Alternatively you can build a "tally" table on-the-fly to join with

SELECT car, id
  FROM car2 s JOIN (
     SELECT a.N + b.N * 10 + 1 n
       FROM 
    (SELECT 0 AS 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) a
   ,(SELECT 0 AS 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) b
  ) t
    ON n BETWEEN 1 AND s.count
 ORDER BY car

You can adjust the number of rows generated by the derived table per your needs. This particular one generates 100 rows.

Here is a dbfiddle demo for both queries

Output:

+--------------+----+
| car          | id |
+--------------+----+
| Fusion       |  1 |
| Honda CRV    |  3 |
| Toyota Camry |  4 |
| Fusion       |  1 |
| Toyota Camry |  4 |
| Toyota Camry |  4 |
+--------------+----+

Upvotes: 2

Related Questions