更新时间:2022-11-27 15:38:31
您可以使用 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)