when I execute following query.
select 30,60,90,120,150,180 from table
I get output given below 
But my desire output like, Want out in only one column.
sequence 30 60 90 120 150 180
Is this possible?
Add a comment
|
4 Answers
Use UNION ALL that will work in all major RDBMSes
SELECT 30 "sequence" UNION ALL
SELECT 60 UNION ALL
SELECT 90 UNION ALL
SELECT 120 UNION ALL
SELECT 150 UNION ALL
SELECT 180
or use postgres' generate_series() function
SELECT *
FROM generate_series(30, 180, 30) "sequence";
Output:
| SEQUENCE | |----------| | 30 | | 60 | | 90 | | 120 | | 150 | | 180 |
Here is SQLFIddle demo for both queries
1 Comment
Bohemian
I would delete the first version. The second one is the (only) way up go.
As other answers mention, generate_series is the way to go when the data you want to generate is a even spaced series like this. If not, I like the version by a_horse_with_no_name. Another way is:
select unnest('{30,60,90,120,150,180}'::int[]) as numbers
Comments
As @peterm said, the way to go in PostgreSQL would be to use generate_series().
In other RDBMS which support recursive cte, you can use it like this:
with recursive cte(sequence) as (
select 30
union all
select sequence + 30
from cte
where sequence < 180
)
select *
from cte
Also, if your RDMBS supports window functions, and you have some table, and you know there're always exists at least 6 rows in this table, you can do this :)
select
row_number() over(order by id) * 30
from temp
limit 6
