CODEWITHSUNDEEP
mysqlsql
Steven
Steven

Reputation: 13975

Select Number Into Multiple Rows

I have a select on a table which selects a number

SELECT * FROM table

And I generates something that looks like

| column |
|   2    |
|   5    |

I am wondering if there is a way to turn that 2 and 5 into multiple rows of 2 and 5. So basically

| column |
|   2    |
|   2    |
|   5    |
|   5    |
|   5    |
|   5    |
|   5    |

Edit: As asked by a few what my ultimate goal is, is I am really trying to accomplish is a sort of ballot select, basically I store the number of ballots a person gets as a # and I want to turn that # into multiple rows so I can transfer it over to another program for the winner selection.

Upvotes: 0

Views: 58

Answers (3)

genespos
genespos

Reputation: 3311

As a workaround you can add a table with two columns like:

|Id|Numbers|
|1 |   1   |
|2 |   2   |
|3 |   2   |
|4 |   3   |
|5 |   3   |
|6 |   3   |
|7 |   4   |
|. |  ...  |
|n |   n   |

Then you can use a 

LEFT JOIN ON numbers = column

Otherwise you need to use a stored procedure with a loop depending on 'column' value

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do it using a numbers (aka tally) table:

SELECT col
FROM mytable AS t1
INNER JOIN (
  SELECT @rn := @rn + 1 AS num
  FROM (
     SELECT 0 AS n UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ) AS x
  CROSS JOIN (
     SELECT 0 AS n UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0  
            UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 ) AS y
  CROSS JOIN (SELECT @rn := 0) AS var          
) AS t2 ON t1.col >= t2.num
ORDER BY col

The above query uses a numbers table with a range of values [1-100]. If your column contains bigger values then you have to grow the numbers table using additional CROSS JOIN operations.

Demo here

Upvotes: 1

JosephStyons
JosephStyons

Reputation: 58685

You can grow it exponentially with repeated joins to itself.

But what are you really trying to accomplish?

select
  a.*
from
  table a
  left join table b on 1=1
  left join table c on 1=1

Upvotes: 0

Related Questions