22,209
社区成员
发帖
与我相关
我的任务
分享
有一个表 Tab1只有一条数据 :
a b c d.....n
-----------------------
10 20 25 30....m
但是多少列不确定。
我想得到它有多少列就是多少行数据
col value
------ -------
a 10
b 20
c 25
d 30
......
--> 测试数据: #tb
if object_id('tb') is not null drop table tb
go
create table tb (a int,b int,c int,d int,n varchar(1))
insert into tb
select 10,20,25,30,'m'
declare @s varchar(4000)
select @s= isnull(@s,'select cast(')+name+' as varchar(10)) from tb union all select cast('
from syscolumns where id=object_id('tb')
set @s=substring(@s,1,len(@s)-22)
exec(@s)
----------
10
20
25
30
m
(5 行受影响)
select 'a' as col,
a
from tab1
union all
select 'b' as col,
b
from tab1
union all
select 'c' as col,
c
from tab1
....