MariaDB 10.0: Split 1 row into many based on column value

Question

I have a table that looks something like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 3   | item c | 4     |

How can I query the database so that 1 row is shown per count? I.e. like this:

| id  | item   | count |
| --- | ------ | ----- |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 1   | item a | 3     |
| 2   | item b | 2     |
| 2   | item b | 2     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |
| 3   | item c | 4     |

In case it helps, I've created a DB fiddle: https://www.db-fiddle.com/f/wRZgBYkDM18c5fk7tgkBkA/0

What is the purpose of doing this ? Depending on more details, maybe there is other workaround possible. Normally, it is the other way around. I doubt if your current requirement is doable in SQL. You should really consider handling this in application code (eg: PHP, Java, C++) — Madhur Bhaiya, Nov 02 '18 at 04:19
Consider using SEQUENCE Storage Engine in MariaDB to generate records with numbers and join them with your table (see https://mariadb.com/kb/en/library/sequence-storage-engine/). — Yuri Lachin, Nov 02 '18 at 08:41
@MadhurBhaiya I want to query the DB and pick a random item weighted by the count. Something like so: `SELECT * FROM (-- SUBQUERY THAT GENERATES WEIGHTED TABLE --) ORDER BY RAND() LIMIT 1` — Zaki Aziz, Nov 02 '18 at 15:34
@YuriLachin I'm on Maria DB 10.0 which does not come with the sequence engine installed. I also do not have the ability to install it. — Zaki Aziz, Nov 02 '18 at 15:35
@Xecure now your problem statement makes sense; I would suggest you to edit the problem to "pick a random item weighted by the count" and change description accordingly. Surely, there are some approaches possibly (eg: user-defined variables, etc). Based on edit, you should definitely get some answers. Current problem is most likely not solvable in SQL. — Madhur Bhaiya, Nov 02 '18 at 17:14

score 0 · Answer 1 · answered Nov 02 '18 at 18:44

Possibly SQL isn't the best option to solve in a simple way what you need, however, an alternative is to use stored procedures, cursors and a temporary table (the example is in MariaDB 10.3.9, but with some changes it will work in MariaDB 10.0):

DELIMITER //

CREATE PROCEDURE `sp_test`()
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS `temp_items`
  SELECT `id`, `item`, `count`
  FROM `items`
  LIMIT 0;

  FOR `repeat_row` IN (
    SELECT `id`, `item`, `count`
    FROM `items`
  ) DO
    FOR `current` IN 1..`repeat_row`.`count`
      DO
      INSERT INTO `temp_items`
      SELECT
        `repeat_row`.`id`,
        `repeat_row`.`item`,
        `repeat_row`.`count`;
    END FOR;
  END FOR;

  SELECT `id`, `item`, `count`
  FROM `temp_items`;
  DROP TEMPORARY TABLE IF EXISTS `temp_items`;
END//

DELIMITER ;

See dbfiddle.

score 0 · Answer 2 · answered Nov 06 '18 at 17:22

The query below is ugly but works:

SELECT id, item, count 
FROM (
   SELECT @curRow := @curRow + 1 AS row_number FROM mytable 
   INNER JOIN (SELECT @curRow := 0) AS ROW 
   ON @curRow < (SELECT max(count) from mytable) 
) AS B LEFT JOIN mytable C ON B.row_number <= C.count

NOTE: it only returns correct data if the total number of records in your table is => max(count).

Original approach found in answer here

MariaDB 10.0: Split 1 row into many based on column value

2 Answers2