declare @sql varchar(8000) set @sql = 'select SID,RID' select @sql = @sql + ',sum(case CategoryID when '''+CategoryID+''' then Amount else 0 end) as Category'+cast(CategoryID as varchar(50))+'Amount' from (select CategoryID from Category) as a select @sql = @sql+' from History group by CategoryID,SID,RID'exec(@sql)
declare @sql varchar(8000) set @sql = 'select SID,RID,' SELECT @SQL = @SQL + 'SUM( CASE CategoryID WHEN '+CASE(CATEGORYID AS NVARCHAR(50) ) +' THEN Amount else 0 end) as'+CateName+'-Amount,' from category select @sql = left(@sql,len(@sql)-1) + 'from 销售历史History group by SID,RID'exec (@sql)
yoki(小马哥) 又比我快一点,我老实去干活了,不瞎溜了。
CREATE TABLE Category(CategoryID varchar(10), CateName varchar(50)) insert into category values( '1', '类别一') insert into category values('2' , '类别二' ) insert into category values('3' , '类别三' )create table History(SID int, RID int, CategoryID varchar(10), Amount int) insert into history values(1 , 1 , '1' , 5) insert into history values(1 , 1 , '2' , 6 ) insert into history values(1 , 1 , '3' , 4 ) insert into history values(1 , 2 , '2' , 6 ) declare @sql varchar(8000) set @sql = 'select SID,RID' select @sql = @sql + ',sum(case CategoryID when '''+CategoryID +''' then Amount else 0 end) as Category'+ CategoryID +'Amount' from (select CategoryID from Category) as a select @sql = @sql+' from History group by SID,RID'exec(@sql) drop table category drop table history
declare @sql varchar(8000) set @sql = 'select SID,RID' select @sql = @sql + ',sum(case cast(CategoryID as varchar(10)) when '''+cast(CategoryID as varchar(10)) +''' then Amount else 0 end) as Category'+ cast(CategoryID as varchar(10)) +'Amount' from (select CategoryID from Category) as a select @sql = @sql+' from History group by SID,RID' exec(@sql)
set @sql = 'select SID,RID'
select @sql = @sql + ',sum(case CategoryID when '''+CategoryID+''' then Amount else 0 end) as Category'+cast(CategoryID as varchar(50))+'Amount'
from (select CategoryID from Category) as a
select @sql = @sql+' from History group by CategoryID,SID,RID'exec(@sql)
set @sql = 'select SID,RID,'
SELECT @SQL = @SQL + 'SUM( CASE CategoryID WHEN '+CASE(CATEGORYID AS NVARCHAR(50) ) +' THEN Amount else 0 end) as'+CateName+'-Amount,'
from category select @sql = left(@sql,len(@sql)-1) +
'from 销售历史History group by SID,RID'exec (@sql)
insert into category values( '1', '类别一')
insert into category values('2' , '类别二' )
insert into category values('3' , '类别三' )create table History(SID int, RID int, CategoryID varchar(10), Amount int)
insert into history values(1 , 1 , '1' , 5)
insert into history values(1 , 1 , '2' , 6 )
insert into history values(1 , 1 , '3' , 4 )
insert into history values(1 , 2 , '2' , 6 ) declare @sql varchar(8000)
set @sql = 'select SID,RID'
select @sql = @sql + ',sum(case CategoryID when '''+CategoryID +''' then Amount else 0 end) as Category'+ CategoryID +'Amount'
from (select CategoryID from Category) as a
select @sql = @sql+' from History group by SID,RID'exec(@sql)
drop table category
drop table history
set @sql = 'select SID,RID'
select @sql = @sql + ',sum(case cast(CategoryID as varchar(10)) when '''+cast(CategoryID as varchar(10)) +''' then Amount else 0 end) as Category'+ cast(CategoryID as varchar(10)) +'Amount'
from (select CategoryID from Category) as a
select @sql = @sql+' from History group by SID,RID'
exec(@sql)