Select many values as one column, not a column for each value, SQL Server

user_10206658 0
Viewed 54

If you do this query in SQL Server it will give you 15 different columns- one for each postcode.

SELECT 
    'CB89RX', 'CB259BL', 'CB19BF', 'CB245HS', 'CB30AP', 'CB12LJ', 
    'CB21RB', 'CB28PX', 'CB28PE', 'CB250HX', 'CB231HN', 'CB58TD', 
    'CB246AY', 'CB42QT', 'CB249JA' AS A

What I want is 1 column that contains all 15 values. How can I do this?

select sql-server sql
4 Answers

You can try using UNPIVOT

select u.postcode
    from tablename
    unpivot
    (
      postcode
      for val in (CB89RX, CB259BL, CB19BF, CB245HS, CB30AP, CB12LJ, CB21RB, CB28PX, CB28PE, CB250HX, CB231HN, CB58TD, CB246AY, CB42QT, CB249JA)
    ) u;
user_69148640

I would like to use From .... VALUES

Select 
    val
From 
  (
  VALUES
    ('CB89RX'), 
    ('CB259BL'), 
    ....
  ) AS T (val)

sqlfiddle

user_51760710

You can use union : 

SELECT 'CB89RX' as PostalCode
Union
Select  'CB19BF'  as PostalCode 
Union 
Select 'CB245HS' as PostalCode 
Union 
...
user_99408030

Try this to avoid repetitive use of as .

SELECT   'CB89RX'  as A
   union 
   select  'CB259BL' 
   union 
   select 'CB19BF' 
   union 
   select 'CB245HS'
   union 
   select 'CB30AP'
   union 
   select    'CB12LJ'
   union 
   select    'CB21RB'
   union 
   select    'CB28PX'   
   union 
   select    'CB28PE'
   union 
   select    'CB250HX'
   union 
   select 'CB231HN'
   union 
   select 'CB58TD'
   union 
   select 'CB246AY'
   union 
   select 'CB42QT'
   union 
   select 'CB249JA'
user_108778600
Related