declare @sql varchar(8000)
set @sql = 'select 产品'
select @sql = @sql + ',sum(case 退出原因 when '''+退出原因+''' then 1 else 0 end) as ['+退出原因+'供应商个数],sum(case 退出原因 when '''+退出原因+''' then 使用时间 else 0 end) as ['+退出原因+'使用时间]'
from (select distinct 退出原因 from 有一表) as aselect @sql = @sql+' from 有一表 group by 产品'exec(@sql)
go
set @sql = 'select 产品'
select @sql = @sql + ',sum(case 退出原因 when '''+退出原因+''' then 1 else 0 end) as ['+退出原因+'供应商个数],sum(case 退出原因 when '''+退出原因+''' then 使用时间 else 0 end) as ['+退出原因+'使用时间]'
from (select distinct 退出原因 from 有一表) as aselect @sql = @sql+' from 有一表 group by 产品'exec(@sql)
go
create table #a (产品 varchar(10),数量 int,购入日期 datetime,合格 varchar(100),供应商 varchar(10),退出日期 datetime,退出原因 varchar(10),使用时间 numeric(10,3))
insert #a values('笔',300 , '98-04-16','Yes','极1','02-06-10','损坏', .285)
insert #a values('磁盘',20 , '97-08-09','Yes','极2','02-03-04','故障', .145)insert #a values('画', 30 , '99-12-30','Yes','极2','01-04-05','破烂', .489)
insert #a values('画', 10 , '99-12-30','No',' 极1','30-02-02','遗失', .164)
insert #a values('画', 11 ,'98-11-10','Yes','极1','03-04-15','故障', .753)
insert #a values('磁盘', 50 , '96-12-26','No',' 极2','01-08-07','损坏', .52)
insert #a values('书', 250 , '97-08-09','Yes','极1','30-02-02','破烂', .356)
insert #a values('电脑', 400 , '02-04-10','Yes','极2','03-02-04','遗失', 1.456)
insert #a values('书', 120 , '02-12-16','No','极2','03-03-04','破烂', .695)
insert #a values('鼠标', 110 ,'03-04-05','No','极1','03-04-27','故障', .893)
insert #a values('键盘', 30 , '02-04-10','Yes','极2','02-09-07','损坏', .147)
insert #a values('鼠标', 2 , '02-02-02','No','极2','02-08-06','损坏', .546)
insert #a values('键盘', 3 , '02-04-10','No','极1','02-09-05','损坏', .123)declare @sql varchar(8000)
set @sql = 'select 产品'
select @sql = @sql + ',sum(case 退出原因 when '''+退出原因+''' then 1 else 0 end) as ['+退出原因+'供应商个数],sum(case 退出原因 when '''+退出原因+''' then 使用时间 else 0 end) as ['+退出原因+'使用时间]'
from (select distinct 退出原因 from #a) as aselect @sql = @sql+' from #a group by 产品'exec(@sql)
go
go
drop table #a