有一个表 Tab1只有一条数据 :
a b c d.....n
-----------------------
10 20 25 30....m但是多少列不确定。我想得到它有多少列就是多少行数据col value
------ -------
a 10
b 20
c 25
d 30
......
a b c d.....n
-----------------------
10 20 25 30....m但是多少列不确定。我想得到它有多少列就是多少行数据col value
------ -------
a 10
b 20
c 25
d 30
......
a
from tab1
union all
select 'b' as col,
b
from tab1
union all
select 'c' as col,
c
from tab1
....
这个方法好,另外可以结合系统表syscolumns
select name from syscolumns where id = object_id('表名') 获取该表中的所有字段,然后结合一楼的代码来获取值就可以了。
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 行受影响)