横表如何转纵表（本来想先搜一下，又不知用什么关键字）？

[huyidehyd]

a b c d e f g h
15 9 1 0 1 2 2 0
部分sql语句：
select
(SELECT count(DISTINCT id)
FROM XL_Repair_Item_Info where id>1) as a,(其余类似)
我想知道，两则如何转换？

[zjcxc]

--横向转纵向

--测试的表
select a=15,b=9,c=1,d=0,e=1,f=2,g=2,h=0 into tb

--数据处理语句
declare @s varchar(8000)
set @s=''
select @s=@s+' union all select '+name+' from tb' from syscolumns where object_id('tb')=id
set @s=substring(@s,12,8000)
exec(@s)

--删除测试表
drop table tb

/*--测试结果:
a
-----------
15
9
1
0
1
2
2
0

--*/

[zjcxc]

--纵向转横向
--横向转纵向

--测试的表
select * into tb
from(
select aa='a',bb=15
union all select 'b',9
union all select 'c',1
union all select 'd',0
union all select 'e',1
union all select 'f',2
union all select 'g',2
union all select 'h',0
) a

--数据处理语句
declare @s varchar(8000)
set @s=''
select @s=@s+','+aa+'='+cast(bb as varchar) from tb
set @s=substring(@s,2,8000)
exec('select '+@s)

--删除测试表
drop table tb

[zjcxc]

测试结果

a b c d e f g h
------------------------------- ----------- ----------- ----------- -----------
15 9 1 0 1 2 2 0

[huyidehyd]

谢谢！

[txlicenhe]

测试：
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)

declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')

a
-----------
15
9
1
0
1
2
2
0