问题:
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'