22,207
社区成员
发帖
与我相关
我的任务
分享
create table #t(A int, B int, C int, D int, E int)
insert into #t
select 1, 2, 3, 4, 5
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql=@Sql+','+QUOTENAME(name) FROM tempdb.sys.columns WHERE object_id=OBJECT_ID('Tempdb..#T')
SET @Sql=STUFF(@Sql,1,1,'')
EXEC('select *
from #T
unpivot(Value for ColName in('+@Sql+')) b')
/*
Value ColName
1 A
2 B
3 C
4 D
5 E*/
create table #t(A int, B int, C int, D int, E int)
insert into #t
select 1, 2, 3, 4, 5
select col,val
from #t a
unpivot(val for col in([A],[B],[C],[D],[E])) b
/*
col val
------------------------ -----------
A 1
B 2
C 3
D 4
E 5
(5 row(s) affected)
*/
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql=@Sql+','+QUOTENAME(name) FROM sys.columns WHERE object_id=OBJECT_ID('T')
SET @Sql=STUFF(@Sql,1,1,'')
EXEC('select *
from T
unpivot(Value for ColName in('+@Sql+')) b')
use tempdb
GO
create table T(A int, B int, C int, D int, E int)
insert into T
select 1, 2, 3, 4, 5
GO
DECLARE @Sql NVARCHAR(max)=''
SELECT @Sql=@Sql+'UNION ALL SELECT '+QUOTENAME(name,'''')+' AS ColName,Value='+name+' FROM T ' FROM sys.columns WHERE object_id=OBJECT_ID('T')
SET @Sql=STUFF(@Sql,1,10,'')
EXEC(@Sql)