Use hardcoded list of values in query

Question

I have a list of ISO2 country codes I want to use in a query.
Something like this:

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

But.. working.

I remember doing such a thing in the past, but I can't find any doc about it anymore. It's a one shot query so I don't mind performance, coding practice or maintainability.

Any ideas?

UPDATE
As Pax noted, it is indeed better practice to have this data in my database for all the good reasons. I understand his opinion because I would answer the same. However, this data already IS in another table, in another database, on another server, on another network..

In order to test my queries I need some quick shot values in a table on this new database. I don't want to configure networks, cross-server queries etc just to test my queries on some real-life data. I hope this explains why I go against the stream for this one shot.

Answer 1

The best way to do this (despite your desire to have them hard-coded) 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 am of the firm belief that data should be stored in tables where it can be easily changed, not embedded within source code or scripts where it's a nightmare to track down and modify.

That's my opinion, others may disagree.

Answer 2

First way using union:

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

The second - 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 places to @x string, assuming that code has len 2. Small restriction of this method is recursive level (for 2005 it is 100)

Answer 3

Pax's answer is correct approach. But if you insist on doing it in-line:

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