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

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?

4 answers

  • answered 2019-01-11 05:21 fa06

    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;
    

  • answered 2019-01-11 05:23 pascal sanchez

    You can use union :

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

  • answered 2019-01-11 05:40 Muhammad Waheed

    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' 
    

  • answered 2019-01-11 05:46 D-Shih

    I would like to use From .... VALUES

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

    sqlfiddle