multiple rows in query from one row MySQL

Question

I have a MySQL table as below. I want to create a query to return multiple rows for each entry based on the count of "seed" as in my example. Is this possible on MySQL?

Table:

-------------------------------
| ID      | Name    | Seed    |
| 1       | Entry 1 | 2       |
| 2       | Entry 2 | 3       |
| 3       | Entry 3 | 1       |
| 4       | Entry 4 | 2       |
-------------------------------

Results:

ID    Name     Seed
1     Entry 1  2
1     Entry 1  2
2     Entry 2  3
2     Entry 2  3
2     Entry 2  3
3     Entry 3  1
2     Entry 2  2
2     Entry   2

Have you tried anything? It is possible using cursors, create a procedure etc. — iiro
– iiro, Commented Feb 27, 2013 at 6:55
I have been googling and all that I can seem to find is combining multiple rows into one, and one solution using php but I need to do this in MySQL. I honestly don't even know where to start. Do you have any recommendations for search queries or tutorials for doing this? — scholzr
– scholzr, Commented Feb 27, 2013 at 7:44

Craig Neil Brown · Accepted Answer · 2013-02-27 07:56:37Z

1

You can introduce another table of sequential seed values. Then your query can join to it using >=.

answered Feb 27, 2013 at 7:56

Craig Neil Brown

613 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:49:19Z

When you need multiple rows, you need integer values by which you gonna multiple your rows. So my example will be with Subquery which holds integer values.

Query:

SQLFIDDLEExample

SELECT t.ID, t.Name, t.Seed
FROM Table1 t
LEFT JOIN (SELECT
    (TWO_1.SeqValue + 
     TWO_2.SeqValue + 
     TWO_4.SeqValue + 
     TWO_8.SeqValue + 
     TWO_16.SeqValue) SeqValue
FROM
    (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
)t2
ON t2.SeqValue < t.SEED

Result:

| ID |    NAME | SEED |
-----------------------
|  1 | Entry 1 |    2 |
|  1 | Entry 1 |    2 |
|  2 | Entry 2 |    3 |
|  2 | Entry 2 |    3 |
|  2 | Entry 2 |    3 |
|  3 | Entry 3 |    1 |
|  4 | Entry 4 |    2 |
|  4 | Entry 4 |    2 |

Collectives™ on Stack Overflow

multiple rows in query from one row MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related