日期:2014-05-17 浏览次数:20735 次
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+' select '+quotename(Name,'''')+', '+quotename(Name,'''')
+'='+quotename(Name) +' from tb '
from syscolumns where ID=object_id('tb')
exec (@s)
if object_id('tb') is not null
drop table tb
go
create table tb(a int,b int,c int)
insert tb
values(1,1,1)
select 'a' as name,a from tb
union all
select 'b' as name,b from tb
union all
select 'c' as name,c from tb
/*
name a
a 1
b 1
c 1
*/
if object_id('tb') is not null
drop table tb
go
create table tb(a int,b int,c int)
insert tb
values(1,1,1)
select e,n
from tb
unpivot
(
n for e in (a,b,c)
)t
select e,n
from tb
unpivot
(
n for e in (a,b,c)
)t
/*
e n
a 1
b 1
c 1
*/
create table 表
(a int,b int,c int)
insert into 表(a,b,c)
select 1,1,1
select col,val
from 表 a
unpivot(val for col in(a,b,c)) p
/*
col val
------------- -------------
a 1
b 1
c 1
(3 row(s) affected)
*/