declare @s varchar(8000)
set @s='select a.company_name'
select @s=@s+','+quotename(product_year)+'=sum(case b.product_year when '+quotename(product_year)+' then 1 else 0 end)'
from 表2 group by product_year order by product_year
set @s=@s+' from 表1 a,表2 b where a.company_id=b.company_id group by a.company_name'
exec(@s)
set @s='select a.company_name'
select @s=@s+','+quotename(product_year)+'=sum(case b.product_year when '+quotename(product_year)+' then 1 else 0 end)'
from 表2 group by product_year order by product_year
set @s=@s+' from 表1 a,表2 b where a.company_id=b.company_id group by a.company_name'
exec(@s)
insert into 表1 values(2,'IBM')insert into 表2 values(1,'2003',1)
insert into 表2 values(1,'2004',1)
insert into 表2 values(2,'2004',1)
insert into 表2 values(1,'2005',1)
insert into 表2 values(2,'2005',1)
insert into 表2 values(3,'2005',1)insert into 表2 values(1,'2003',2)
insert into 表2 values(2,'2003',2)
insert into 表2 values(1,'2004',2)
insert into 表2 values(1,'2005',2)
insert into 表2 values(2,'2005',2)
select 表1.*,product_year,count(*) as num
into #tmp
from 表1,表2
where 表1.company_id = 表2.company_id
group by 表1.company_id,company_name,product_yearselect distinct product_year
into #tmpYear
from #tmp
order by product_year descdeclare @sql varchar(2000)
select @sql = 'select company_id,company_name';
select @sql = @sql + ',max(case product_year when '+convert(varchar,product_year)+' then num end) ['+convert(varchar,product_year)+']'
from #tmpYear;
set @sql = @sql+' from #tmp group by company_id,company_name order by company_id';
exec(@sql);
create table 表1(company_id int, company_name varchar(60))create table 表2(product_id int, product_year char(4),company_id int)insert into 表1 values(1,'微软')
insert into 表1 values(2,'IBM')insert into 表2 values(1,'2003',1)
insert into 表2 values(1,'2004',1)
insert into 表2 values(2,'2004',1)
insert into 表2 values(1,'2005',1)
insert into 表2 values(2,'2005',1)
insert into 表2 values(3,'2005',1)insert into 表2 values(1,'2003',2)
insert into 表2 values(2,'2003',2)
insert into 表2 values(1,'2004',2)
insert into 表2 values(1,'2005',2)
insert into 表2 values(2,'2005',2)declare @cstr varchar(8000)set @cstr=''
select @cstr=@cstr +quotename(product_year)+'=sum(case when b.product_year='''
+ product_year + ''' then 1 else 0 end) ,' from 表2 group by product_year
set @cstr=stuff(@cstr,len(@cstr),1,'')
set @cstr = 'select max(a.company_name) as 公司,'+ @cstr
+'from 表1 a,表2 b where a.Company_id = b.company_id group by a.company_ID'exec(@cstr)
declare @s varchar(8000)
set @s='select a.company_name'
select @s=@s+','+quotename(product_year)+'=sum(case b.product_year when '+convert(varchar,product_year)+' then 1 else 0 end)'
from 表2 group by product_year order by product_year
set @s=@s+' from 表1 a,表2 b where a.company_id=b.company_id group by a.company_name'
exec(@s)
declare @s varchar(8000)
set @s='select a.company_name'
select @s=@s+','+quotename(product_year)+'=sum(case b.product_year when '+convert(varchar,product_year)+' then 1 else 0 end)'
from 表2 group by product_year order by product_year desc
set @s=@s+' from 表1 a,表2 b where a.company_id=b.company_id group by a.company_name'
exec(@s)