CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2017
MHOOS
MHOOS

Reputation: 5306

Return a constant set of key value pair

Imagine I have something like the following

SELECT 0 AS 'Key','No' AS 'Value'
UNION
SELECT 1 AS 'Key','YES' AS 'Value'
UNION  
SELECT 2 AS 'Key','Maybe' AS 'Value'
....
....

How can I make above statement more readable so I can accommodate more constant key/value pair in above list in a single select statement? I don't want to create table variable or create a complex sql statement. Just a single select statement returning bunch of constant key/pair values.

Upvotes: 0

Views: 704

Answers (3)

paparazzo
paparazzo

Reputation: 45096

Understand you don't want to create a table variable
I use the accepted answer a lot +1
Just pointing out a table variable lets you declare type and primary key 

declare @tbl table ([key] tinyint primary key,  [value] varchar(12));
insert into @tbl values (1, 'one')
                      , (2, 'two')
                      , (3, 'three');
select * from @tbl order by [key];

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use VALUES:

SELECT *
FROM (VALUES 
    (0, 'No'),
    (1, 'Yes'),
    (2, 'Maybe')
) t([Key], Value)

Table Value Constructor

Upvotes: 5

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Using a table value constructor.

VALUES ((0,'NO'),(1,'YES'),(2,'MAYBE'))

Upvotes: 2

Related Questions