Use a hardcoded list of values ​​in a query

Question

I have a list of ISO2 country codes that I want to use in the request. Something like that:

select cou, 128,13, 1 from ('AD', 'AE', 'AF', 'AG', 'AI', 'AL', 'AM', [snip]) as cou 

But I work.

I remember how this was done in the past, but I can’t find anything else. This is a one-time request, so I am not opposed to performance, coding practices or maintainability.

Any ideas?

UPDATE
As Pax noted, it’s actually better to use this data in my database for all good reasons. I understand his opinion, because I would answer the same thing. However, this data is already in another table, in another database, on another server, on a different network.

To check my queries, I need some quick shot values ​​in a table in this new database. I do not want to configure networks, cross-server requests, etc., Just to check my requests for some real data. Hope this explains why I am going upstream for this shot.

Answer 1

The first way to use union:

 select 'AD' union all select 'AE' .... 

The second is not obvious, but elegant - using recursive queries;

 declare @x as varchar(200) set @x = 'ADAEAFAGAIALAM' ;with FakeTbl AS ( SELECT substring(@x, 1, 2) sval, 0 as ROWN WHERE LEN(@x) > 0 UNION ALL SELECT substring(@x, (it.ROWN+1)*2+1, 2) sval, it.ROWN+1 as ROWN FROM FakeTbl it WHERE LEN(@x) > (it.ROWN+1)*2 ) select sval, ROWN from FakeTbl 

You put @x on the line, assuming the code has len 2. The small limitation of this method is the recursive level (for 2005 it is 100)

Answer 2

To achieve this, you can use the Values ​​keyword.

 select col1, 128 col2, 13 col3, 1 col4 from ( values ('AD'), ('AE'), ('AF'), ('AG'), ('AI'), ('AL'), ('AM')) as x (col1) 

Answer 3

The best way to do this (despite your desire to hardcode them) is to create a table of country codes:

 create table iso2_codes ( code char(2) primary key ) insert into iso2_codes (code) vales ('AD'); insert into iso2_codes (code) vales ('AE'); : : : insert into iso2_codes (code) vales ('AM'); 

Then just use

 select code, 128, 13, 1 from iso2_codes; 

I strongly believe that data should be stored in tables, where it can be easily changed, and not embedded in source code or scripts, where it is a nightmare to track and modify.

What is my opinion, others may not agree.

Answer 4

Pax's answer is the right approach. But if you insist on doing this online:

 select cou, 128,13, 1 from (select 'AD' as cou union all select 'AE' union all select 'AF' union all select 'AG' union all select 'AI' union all select 'AL' union all select 'AM') as X