表 a 记录
id val tv
1 100 10
2 200 20
4 400 40
---------
不存在id为3的记录用0代替
得到这样的查询结果:
--------------------
id1 val1 tv1 id2 val2 tv2 id3 val3 tv3 id4 val4 tv4
1 100 10 2 200 20 3 0 0 4 400 40
--------------------
答案(csdn邹建做答)
--测试
--测试数据
create table a(id int,val int,tv int)
insert a select 1,100,10
union all select 2,200,20
union all select 4,400,40
go
--查询
declare @s varchar(8000),@i1 int,@i2 int
select @i1=min(id) --如果固定从1开始,则直接赋值: @i1=1
,@i2=max(id)
,@s=''
from a
while @i1<=@i2
begin
select @s=@s+',id'+cast(@i1 as varchar)+'='+cast(id as varchar)
+',val'+cast(@i1 as varchar)+'='+cast(val as varchar)
+',tv'+cast(@i1 as varchar)+'='+cast(tv as varchar)
from a where id = @i1
if @@rowcount=0
select @s=@s+',id'+cast(@i1 as varchar)+'='+cast(@i1 as varchar)
+',val'+cast(@i1 as varchar)+'=0'
+',tv'+cast(@i1 as varchar)+'=0'
set @i1=@i1+1
end
set @s=stuff(@s,1,1,'')
exec('select '+ @s)
go
--删除测试
drop table a
/*--测试结果
id1 val1 tv1 id2 val2 tv2 id3 val3 tv3 id4 val4 tv4
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
1 100 10 2 200 20 0 0 0 4 400 40
--*/
记录不能太多