How to select the hardcoded array in SQL?

Question

In SQL, if I want to select a hardcoded value, I can do SELECT 1; for example, which would return a table with one column and one row with value 1. How can I select the hardcoded array in SQL?

So, I want the output to contain one column with two rows, say with values 1 and 2. How can I do that?

I tried SELECT 1, 2; but that returns a table with one row but two columns, what I do not want. I also tried SELECT (1, 2); and SELECT IN(1, 2); but none of them works.

I use the Microsoft Structured Query Language server.

I guess you want SELECT 1 UNION ALL SELECT 2, but what's the purpose of that strange idea? — Jonas Metzler
– Jonas Metzler, Commented Sep 21, 2023 at 16:14
This depends on the version of sql server you are using. Anything earlier than 2022 I would use a tally table. For 2022 I would use GENERATE_SERIES. In any version using a hard coded list of values is rather cumbersome and strange. — Sean Lange
– Sean Lange, Commented Sep 21, 2023 at 16:26
MS SQL server doesn't support array data type, if you meant to get multiple rows from values, then you could use from (values (1), (2), (3) ...) t(ColName) syntax. — Cetin Basoz
– Cetin Basoz, Commented Sep 21, 2023 at 16:37
maybe this could help stackoverflow.com/questions/1732613/… — nbk
– nbk, Commented Sep 21, 2023 at 16:56
Are you looking for the TABLE VALUE CONSTRUCTOR? SELECT a,b FROM (VALUES (1,2)) a (a,b) learn.microsoft.com/en-us/sql/t-sql/queries/… — Patrick Hurst
– Patrick Hurst, Commented Sep 21, 2023 at 18:07

David Browne - Microsoft · Accepted Answer · 2023-09-21 16:29:20Z

4

select *
from (values (1),(2)) v(value)

or

select 1 value
union all
select 2 value

or (for SQL Server 2022+ and Azure SQL)

select value
from generate_series(1,2)

answered Sep 21, 2023 at 16:29

community wiki

David Browne - Microsoft

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

1 Comment

Joel Coehoorn Over a year ago

Hurray for generate_series(). Now just to wait for my vendor to support 2022.

Collectives™ on Stack Overflow

How to select the hardcoded array in SQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related