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',...

问题:

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?


回答1:

I would like to use From .... VALUES

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

sqlfiddle


回答2:

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;

回答3:

You can use union :

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

回答4:

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' 
  • 发表于 2019-01-16 19:16
  • 阅读 ( 166 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除