declare @s varchar(8000) set @s = ''select @s = @s +','+Color+'= sum(case Color when'''+Color+'''then Or else 0 end)' from A group by Color order by Color exec ('select No '+@s+' from A group by No order by No')
select a.NO,(select OR from T1 where a.NO = T1.NO AND T1.Color = 1),(select OR from T1 where a.NO= T1.NO AND T1.COLOR = 4),(select OR from T1 where a.NO = T1.NO AND T1.COLOR = 5),(select OR from T1 where a.NO = T1.NO AND T1.Color = 6) from (select distinct NO from T1) a 没有测试 搂主测试哈 应该没有问题 如果color还需要其他列那就继续用(select OR from T1 where a.NO = T1.NO AND T1.Color = *)
create table A(NO nvarchar(10),Color nvarchar(10),[OR] nvarchar(10)) declare @s varchar(8000) set @s = '' select @s = @s +','''+Color+'''= sum(case Color when '''+Color+''' then [Or] else 0 end)' from A group by Color order by Color exec ('select No '+@s+' from A group by No order by No')
create table a(No int,color int, [or] decimal(18,4))insert into a select 8,1,8250 insert into a select 8,4,307180 insert into a select 8,5,462250 insert into a select 8,6,221600 insert into a select 9,1,37100 insert into a select 9,4,213750 insert into a select 9,5,470100 insert into a select 9,6,244000 insert into a select 82,1,121995 insert into a select 82,2,27800 insert into a select 82,3,14460 insert into a select 82,4,242260 insert into a select 83,1,128450 insert into a select 83,2,43450 insert into a select 83,3,8400 insert into a select 83,4,247700 insert into a select 84,1,68260 insert into a select 84,2,24700 insert into a select 84,3,600 insert into a select 84,4,159360 insert into a select 85,1,93540 insert into a select 85,2,1350 insert into a select 85,3,11000 insert into a select 85,4,233150 insert into a select 86,1,13900 insert into a select 86,2,10850 insert into a select 86,3,8450 insert into a select 86,4,12600 insert into a select 86,5,37650 insert into a select 86,6,141750 insert into a select 86,7,331550 insert into a select 87,1,1150 insert into a select 87,2,24418 insert into a select 87,3,17710 insert into a select 87,4,28980 insert into a select 87,5,27908 insert into a select 87,6,124700 insert into a select 87,7,327150 insert into a select 88,1,61600 insert into a select 88,2,850 insert into a select 89,1,59260 declare @s varchar(8000) set @s = ''select @s = @s +','+' sum(case Color when'''+cast(Color as char(1))+'''then [Or] else 0 end) as ''' + cast(Color as char(1))+ '''' from A group by Color order by Color--print ('select No '+@s+' from A group by No order by No') exec ('select No '+@s+' from A group by No order by No')
set @s = ''select @s = @s +','+Color+'= sum(case Color when'''+Color+'''then Or else 0 end)'
from A group by Color order by Color
exec ('select No '+@s+' from A group by No order by No')
搂主测试哈
应该没有问题
如果color还需要其他列那就继续用(select OR from T1 where a.NO = T1.NO AND T1.Color = *)
declare @s varchar(8000)
set @s = ''
select @s = @s +','''+Color+'''= sum(case Color when '''+Color+''' then [Or] else 0 end)'
from A group by Color order by Color
exec ('select No '+@s+' from A group by No order by No')
create table a(No int,color int, [or] decimal(18,4))insert into a select 8,1,8250
insert into a select 8,4,307180
insert into a select 8,5,462250
insert into a select 8,6,221600
insert into a select 9,1,37100
insert into a select 9,4,213750
insert into a select 9,5,470100
insert into a select 9,6,244000
insert into a select 82,1,121995
insert into a select 82,2,27800
insert into a select 82,3,14460
insert into a select 82,4,242260
insert into a select 83,1,128450
insert into a select 83,2,43450
insert into a select 83,3,8400
insert into a select 83,4,247700
insert into a select 84,1,68260
insert into a select 84,2,24700
insert into a select 84,3,600
insert into a select 84,4,159360
insert into a select 85,1,93540
insert into a select 85,2,1350
insert into a select 85,3,11000
insert into a select 85,4,233150
insert into a select 86,1,13900
insert into a select 86,2,10850
insert into a select 86,3,8450
insert into a select 86,4,12600
insert into a select 86,5,37650
insert into a select 86,6,141750
insert into a select 86,7,331550
insert into a select 87,1,1150
insert into a select 87,2,24418
insert into a select 87,3,17710
insert into a select 87,4,28980
insert into a select 87,5,27908
insert into a select 87,6,124700
insert into a select 87,7,327150
insert into a select 88,1,61600
insert into a select 88,2,850
insert into a select 89,1,59260
declare @s varchar(8000)
set @s = ''select @s = @s +','+' sum(case Color when'''+cast(Color as char(1))+'''then [Or] else 0 end) as ''' + cast(Color as char(1))+ ''''
from A group by Color order by Color--print ('select No '+@s+' from A group by No order by No')
exec ('select No '+@s+' from A group by No order by No')