CODEWITHSUNDEEP
mysqlsql
Serafeim
Serafeim

Reputation: 15084

Mysql return enumeration of numbers in different rows

I want to return the numbers 1,2,3,4 from mysql in different rows.

If I run

select 1,2,3,4 then I will get a single row with these four numbers.

How can I get four different rows each with a single number ?

Please don't answer me to create a table containing these numbers ! Also the use case is for a jasper report I want to make.

Upvotes: 2

Views: 84

Answers (5)

Bhushan
Bhushan

Reputation: 6181

Try using UNION:

SELECT 1 
FROM   tablename 
UNION 
SELECT 2 
FROM   tablename 
UNION 
SELECT 3 
FROM   tablename 
UNION 
SELECT 4 
FROM   tablename

Upvotes: 1

Nabil Kadimi
Nabil Kadimi

Reputation: 10394

The query 

SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4;

The result 

+---+
| 1 |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

Upvotes: 1

sqlab
sqlab

Reputation: 6436

e.g 

select 1 from  ..
union  
select 2 from  ..
union  
select 3 from ..
union  
select 4 from ..

Upvotes: 1

peter.petrov
peter.petrov

Reputation: 39437

Try using UNION

select 1 as col_name
UNION
select 2
UNION
select 3
UNION
select 4

If some of your values occur more than once (say you have two 1s and you want them both in your returned rows), then you may want to use UNION ALL instead of UNION.

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460058

You could use UNION ALL to concat the rows:

SELECT 1 AS ColumnName
UNION ALL 
SELECT 2 AS ColumnName
UNION ALL 
SELECT 3 AS ColumnName
UNION ALL 
SELECT 4 AS ColumnName

Demo

Upvotes: 5

Related Questions