各位对不起,YZQYZQ委托我改正原贴一点小小的错误!有一个表t1如下:
id ax
1 67
1 78
1 90
1 76
2 34
2 45
2 56
2 89
3 23
3 56
3 65
3 78
4 50
4 70
4 87
4 98
5 19
5 42
5 56
5 89想得到下面的结果
id a1 a2 a3 a4
1 67 78 76 90
2 34 56 45 89
3 23 56 78 65
4 87 98 70 50
5 56 89 42 19
请各位高手指教!
id ax
1 67
1 78
1 90
1 76
2 34
2 45
2 56
2 89
3 23
3 56
3 65
3 78
4 50
4 70
4 87
4 98
5 19
5 42
5 56
5 89想得到下面的结果
id a1 a2 a3 a4
1 67 78 76 90
2 34 56 45 89
3 23 56 78 65
4 87 98 70 50
5 56 89 42 19
请各位高手指教!
create table t1(id int,ax int)
insert t1 select 1,67
union all select 1,78
union all select 1,90
union all select 1,76
union all select 2,34
union all select 2,45
union all select 2,56
union all select 2,89
union all select 3,23
union all select 3,56
union all select 3,65
union all select 3,78
union all select 4,50
union all select 4,70
union all select 4,87
union all select 4,98
union all select 5,19
union all select 5,42
union all select 5,56
union all select 5,89
go--处理
declare @id int,@i int,@s varchar(8000)select sid=0,id,ax into #t from t1 order by id
update #t set @i=case id when @id then @i+1 else 1 end
,sid=@i,@id=id
select @i=max(sid),@s='' from #t
while @i>0
select @s=',[a'+cast(@i as varchar)+']=sum(case sid when '+cast(@i as varchar)+' then ax else 0 end)'+@s
,@i=@i-1
exec('select id'+@s+' from #t group by id')
drop table #t
go--删除测试
drop table t1/*--测试结果id a1 a2 a3 a4
----------- ----------- ----------- ----------- -----------
1 67 78 90 76
2 34 45 56 89
3 23 56 65 78
4 50 70 87 98
5 19 42 56 89
--*/
如果还有其他字段,或许可以用更简单一些的办法我作过类似这样的
id ax code
1 67 a1
1 78 a2
1 90 a3
1 76 a4
2 34 a1
2 45 a2
2 56 a3
2 89 a4如果是这样,可以用:
select id ,
sum(case when code='a1' then ax else 0 end) as a1,
sum(case when code='a2' then ax else 0 end) as a2,
sum(case when code='a3' then ax else 0 end) as a3,
sum(case when code='a4' then ax else 0 end) as a4
from t1 group by id菜鸟之见
DECLARE tbla_cursor CURSOR FOR
SELECT [id],ax FROM tbla
ORDER BY [id]
--Open Cursor
close tbla_cursor
OPEN tbla_cursor
create table tbl_tmp
([id] int,a1 int,a2 int,a3 int,a4 int)
delete from tbl_tmp
--Declare Variant
declare @count int
set @count=1
declare @id1 int,@ax1 int
declare @id2 int,@ax2 int
declare @id3 int,@ax3 int
declare @id4 int,@ax4 int
--fetch records into Variantwhile @count<=(select count(distinct([id])) from tbla)
begin
FETCH next FROM tbla_cursor into @id1,@ax1
FETCH next FROM tbla_cursor into @id2,@ax2
FETCH next FROM tbla_cursor into @id3,@ax3
FETCH next FROM tbla_cursor into @id4,@ax4
insert into tbl_tmp
select @count,t1.a1,t2.a2,t3.a3,t4.a4
from (select @id1 id1,@ax1 a1)as t1
left join (select @id2 id2,@ax2 a2)as t2 on t1.id1=t2.id2
left join (select @id3 id3,@ax3 a3)as t3 on t2.id2=t3.id3
left join (select @id4 id4,@ax4 a4)as t4 on t3.id3=t4.id4
set @count= @count+1
end
--query
select * from tbl_tmp