SQL查询仅获取两列中的结果集 [英] SQL query to get the resultset in two columns only
本文介绍了SQL查询仅获取两列中的结果集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这张桌子:
id fName lName Address PostCode ContactNumber
-----------------------------------------------------
1 Tom Daley London EC1 4EQ 075825485665
2 Jessica Ennis Sheffield SF2 3ER 075668956665
3 Joe Bloggs Glasgow G3 2AZ 075659565666
我想要一个查询来给我这样的结果:
I want a query to give me the results like this:
id | label
1 | Tom
1 | Daley
1 | London
1 | EC1 4EQ
1 | 075825485665
2 | Jessica
2 | Ennis
2 | Sheffied
依此类推.
请提供有关如何执行此操作的任何建议.
Any suggestions please on how to do this.
推荐答案
您可以使用 UNPIVOT
函数把列变成行:
You can use the UNPIVOT
function to turn the columns into rows:
select id, value
from yourtable
unpivot
(
value
for col in ([fName], [lName], [Address], [PostCode], [ContactNumber])
) unpiv
unpivot 将要求所有列的数据类型相同.因此,您可能必须对具有与此类似的不同数据类型的任何列执行 cast
/convert
:
The unpivot will require the datatype on all of the columns to be the same. So you might have to perform a cast
/convert
on any columns with different datatypes similar to this:
select id, value
from
(
select id, [fName], [lName], [Address], [PostCode],
cast([ContactNumber] as varchar(15)) [ContactNumber]
from yourtable
) src
unpivot
(
value
for col in ([fName], [lName], [Address], [PostCode], [ContactNumber])
) unpiv;
从 SQL Server 2008 开始,这也可以使用带有 VALUES
的 CROSS APPLY
来编写:
Starting in SQL Server 2008, this can also be written using a CROSS APPLY
with a VALUES
:
select t.id,
c.value
from yourtable t
cross apply
(
values(fName),
(lName),
(Address),
(PostCode),
(cast(ContactNumber as varchar(15)))
) c (value)
这篇关于SQL查询仅获取两列中的结果集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文