我需要生成的结果也是这样就行,有个大哥帮我写了一段,可是数据稍微一多点儿就出错,我也贴上来!不过他这个表结构和我要求的不一样 create table test(Company varchar(20),AdType varchar(20),Score int) insert into test select 'Microsoft','形象广告',88 insert into test select 'Microsoft','产品广告',1 insert into test select 'Microsoft','其他广告',2 insert into test select 'IBM','形象广告',33 insert into test select 'IBM','产品广告',50 insert into test select 'IBM','其他广告',2 insert into test select 'Acer','形象广告',20 insert into test select 'Acer','产品广告',14 insert into test select 'Acer','其他广告',5 gocreate procedure sp_test(@num int) as begin declare @sql varchar(8000) create table #(Company varchar(20),AdType varchar(20),Score int) set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)' exec(@sql)
set @sql='select AdType' select @sql=@sql+','+Company+'=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company set @sql=@sql+' from # group by AdType' exec(@sql) end goexec sp_test 3 /* AdType Acer IBM Microsoft -------------------- ----------- ----------- ----------- 产品广告 14 50 1 其他广告 5 2 2 形象广告 20 33 88 */ exec sp_test 2 /* AdType Acer IBM Microsoft -------------------- ----------- ----------- ----------- 产品广告 14 50 0 其他广告 5 2 0 形象广告 0 33 88 */ exec sp_test 1 /* AdType Acer IBM Microsoft -------------------- ----------- ----------- ----------- 产品广告 0 50 0 其他广告 5 0 0 形象广告 0 0 88 */ go drop procedure sp_test drop table test go
create table test( AdType varchar(20), 厂商01 varchar(20), 厂商02 varchar(20), 厂商03 varchar(20), 厂商04 varchar(20), 厂商05 varchar(20), 厂商06 varchar(20), 厂商07 varchar(20), 厂商08 varchar(20), 厂商09 varchar(20), 厂商10 varchar(20), 厂商11 varchar(20), 厂商12 varchar(20), 厂商13 varchar(20), 厂商14 varchar(20), 厂商15 varchar(20), 厂商16 varchar(20), 厂商17 varchar(20), 厂商18 varchar(20), 厂商19 varchar(20), 厂商20 varchar(20)) insert into test select '产品广告',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 insert into test select '形象广告',10,11,12,13,14,1,2,16,17,18,19,20,3,4,5,6,7,8,9,15 insert into test select '其他广告',16,17,18,19,20,8,9,10,11,12,13,1,2,3,4,5,6,7,14,15 go create procedure sp_test(@num int) as begin create table #t1(Company varchar(20),AdType varchar(20),Score int) create table #t2(Company varchar(20),AdType varchar(20),Score int) declare @s varchar(8000) set @s='' select @s=@s+' union all select '''+name+''',AdType,'+name+' from test' from syscolumns where id=object_id('test') and colid>1 set @s='insert into #t1(Company,AdType,Score)'+stuff(@s,1,10,'') exec(@s)
set @s='insert into #t2 select a.* from #t1 a where a.Company in(select top '+rtrim(@num)+' Company from #t1 where AdType=a.AdType order by Score desc)' exec(@s)
set @s='select AdType' select @s=@s+','+Company+'=max(case Company when '''+Company+''' then Score else 0 end)' from #t2 group by Company set @s=@s+' from #t2 group by AdType' exec(@s) end go exec sp_test 3 /* AdType 厂商03 厂商04 厂商05 厂商10 厂商11 厂商12 厂商18 厂商19 厂商20 -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 产品广告 0 0 0 0 0 0 18 19 20 其他广告 18 19 20 0 0 0 0 0 0 形象广告 0 0 0 18 19 20 0 0 0 */ exec sp_test 2 /* AdType 厂商04 厂商05 厂商11 厂商12 厂商19 厂商20 -------------------- ----------- ----------- ----------- ----------- ----------- ----------- 产品广告 0 0 0 0 19 20 其他广告 19 20 0 0 0 0 形象广告 0 0 19 20 0 0 */ godrop procedure sp_test drop table test
create table test(Company varchar(20),AdType varchar(20),Score int) insert into test select 'Microsoft','形象广告',88 insert into test select 'Microsoft','产品广告',1 insert into test select 'Microsoft','其他广告',2 insert into test select 'IBM','形象广告',33 insert into test select 'IBM','产品广告',50 insert into test select 'IBM','其他广告',2 insert into test select 'Acer','形象广告',20 insert into test select 'Acer','产品广告',14 insert into test select 'Acer','其他广告',5 gocreate procedure sp_test(@num int) as begin declare @sql varchar(8000) create table #(Company varchar(20),AdType varchar(20),Score int) set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)' exec(@sql)
set @sql='select AdType' select @sql=@sql+','+Company+'=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company set @sql=@sql+' from # group by AdType' exec(@sql) end go以上这个方案如果遇到Company中含有()或者()的时候怎么办?目前会出错!
create table test( AdType varchar(20), [厂商01()] varchar(20), [厂商02()] varchar(20), [厂商03()] varchar(20), [厂商04()] varchar(20), [厂商05()] varchar(20), [厂商06()] varchar(20), [厂商07()] varchar(20), [厂商08()] varchar(20), [厂商09()] varchar(20), [厂商10()] varchar(20), [厂商11()] varchar(20), [厂商12()] varchar(20), [厂商13()] varchar(20), [厂商14()] varchar(20), [厂商15()] varchar(20), [厂商16()] varchar(20), [厂商17()] varchar(20), [厂商18()] varchar(20), [厂商19()] varchar(20), [厂商20()] varchar(20)) insert into test select '产品广告',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 insert into test select '形象广告',10,11,12,13,14,1,2,16,17,18,19,20,3,4,5,6,7,8,9,15 insert into test select '其他广告',16,17,18,19,20,8,9,10,11,12,13,1,2,3,4,5,6,7,14,15 gocreate procedure sp_test(@num int) as begin create table #t1(Company varchar(20),AdType varchar(20),Score int) create table #t2(Company varchar(20),AdType varchar(20),Score int) declare @s varchar(8000) set @s='' select @s=@s+' union all select '''+name+''',AdType,['+name+'] from test' from syscolumns where id=object_id('test') and colid>1 set @s='insert into #t1(Company,AdType,Score)'+stuff(@s,1,10,'') exec(@s)
set @s='insert into #t2 select a.* from #t1 a where a.Company in(select top '+rtrim(@num)+' Company from #t1 where AdType=a.AdType order by Score desc)' exec(@s)
set @s='select AdType' select @s=@s+',['+Company+']=max(case Company when '''+Company+''' then Score else 0 end)' from #t2 group by Company set @s=@s+' from #t2 group by AdType' exec(@s) end goexec sp_test 3 /* AdType 厂商03() 厂商04() 厂商05() 厂商10() 厂商11() 厂商12() 厂商18() 厂商19() 厂商20() -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 产品广告 0 0 0 0 0 0 18 19 20 其他广告 18 19 20 0 0 0 0 0 0 形象广告 0 0 0 18 19 20 0 0 0 */ godrop procedure sp_test drop table test
create procedure sp_test(@num int) as begin declare @sql varchar(8000) create table #(Company varchar(20),AdType varchar(20),Score int) set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)' exec(@sql)
set @sql='select AdType' select @sql=@sql+',['+Company+']=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company set @sql=@sql+' from # group by AdType' exec(@sql) end go
create procedure sp_test(@num int) as begin declare @sql varchar(8000) create table #(Company varchar(20),AdType varchar(20),Score int) set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)' exec(@sql)
set @sql='select AdType' select @sql=@sql+',['+Company+']=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company set @sql=@sql+' from test group by AdType' exec(@sql) end go
set @sql=@sql+' from # group by AdType'==>set @sql=@sql+' from test group by AdType'
形象广告 20 33 88 .............................
产品广告 14 50 1 .............................
其他广告 5 2 2 .............................这就是原来的结构
create table test(Company varchar(20),AdType varchar(20),Score int)
insert into test select 'Microsoft','形象广告',88
insert into test select 'Microsoft','产品广告',1
insert into test select 'Microsoft','其他广告',2
insert into test select 'IBM','形象广告',33
insert into test select 'IBM','产品广告',50
insert into test select 'IBM','其他广告',2
insert into test select 'Acer','形象广告',20
insert into test select 'Acer','产品广告',14
insert into test select 'Acer','其他广告',5
gocreate procedure sp_test(@num int)
as
begin
declare @sql varchar(8000)
create table #(Company varchar(20),AdType varchar(20),Score int)
set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)'
exec(@sql)
set @sql='select AdType'
select @sql=@sql+','+Company+'=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company
set @sql=@sql+' from # group by AdType'
exec(@sql)
end
goexec sp_test 3
/*
AdType Acer IBM Microsoft
-------------------- ----------- ----------- -----------
产品广告 14 50 1
其他广告 5 2 2
形象广告 20 33 88
*/
exec sp_test 2
/*
AdType Acer IBM Microsoft
-------------------- ----------- ----------- -----------
产品广告 14 50 0
其他广告 5 2 0
形象广告 0 33 88
*/
exec sp_test 1
/*
AdType Acer IBM Microsoft
-------------------- ----------- ----------- -----------
产品广告 0 50 0
其他广告 5 0 0
形象广告 0 0 88
*/
go
drop procedure sp_test
drop table test
go
AdType varchar(20),
厂商01 varchar(20),
厂商02 varchar(20),
厂商03 varchar(20),
厂商04 varchar(20),
厂商05 varchar(20),
厂商06 varchar(20),
厂商07 varchar(20),
厂商08 varchar(20),
厂商09 varchar(20),
厂商10 varchar(20),
厂商11 varchar(20),
厂商12 varchar(20),
厂商13 varchar(20),
厂商14 varchar(20),
厂商15 varchar(20),
厂商16 varchar(20),
厂商17 varchar(20),
厂商18 varchar(20),
厂商19 varchar(20),
厂商20 varchar(20))
insert into test select '产品广告',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
insert into test select '形象广告',10,11,12,13,14,1,2,16,17,18,19,20,3,4,5,6,7,8,9,15
insert into test select '其他广告',16,17,18,19,20,8,9,10,11,12,13,1,2,3,4,5,6,7,14,15
go
create procedure sp_test(@num int)
as
begin
create table #t1(Company varchar(20),AdType varchar(20),Score int)
create table #t2(Company varchar(20),AdType varchar(20),Score int)
declare @s varchar(8000)
set @s=''
select @s=@s+' union all select '''+name+''',AdType,'+name+' from test' from syscolumns where id=object_id('test') and colid>1
set @s='insert into #t1(Company,AdType,Score)'+stuff(@s,1,10,'')
exec(@s)
set @s='insert into #t2 select a.* from #t1 a where a.Company in(select top '+rtrim(@num)+' Company from #t1 where AdType=a.AdType order by Score desc)'
exec(@s)
set @s='select AdType'
select @s=@s+','+Company+'=max(case Company when '''+Company+''' then Score else 0 end)' from #t2 group by Company
set @s=@s+' from #t2 group by AdType'
exec(@s)
end
go
exec sp_test 3
/*
AdType 厂商03 厂商04 厂商05 厂商10 厂商11 厂商12 厂商18 厂商19 厂商20
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
产品广告 0 0 0 0 0 0 18 19 20
其他广告 18 19 20 0 0 0 0 0 0
形象广告 0 0 0 18 19 20 0 0 0
*/
exec sp_test 2
/*
AdType 厂商04 厂商05 厂商11 厂商12 厂商19 厂商20
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
产品广告 0 0 0 0 19 20
其他广告 19 20 0 0 0 0
形象广告 0 0 19 20 0 0
*/
godrop procedure sp_test
drop table test
insert into test select 'Microsoft','形象广告',88
insert into test select 'Microsoft','产品广告',1
insert into test select 'Microsoft','其他广告',2
insert into test select 'IBM','形象广告',33
insert into test select 'IBM','产品广告',50
insert into test select 'IBM','其他广告',2
insert into test select 'Acer','形象广告',20
insert into test select 'Acer','产品广告',14
insert into test select 'Acer','其他广告',5
gocreate procedure sp_test(@num int)
as
begin
declare @sql varchar(8000)
create table #(Company varchar(20),AdType varchar(20),Score int)
set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)'
exec(@sql)
set @sql='select AdType'
select @sql=@sql+','+Company+'=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company
set @sql=@sql+' from # group by AdType'
exec(@sql)
end
go以上这个方案如果遇到Company中含有()或者()的时候怎么办?目前会出错!
AdType varchar(20),
[厂商01()] varchar(20),
[厂商02()] varchar(20),
[厂商03()] varchar(20),
[厂商04()] varchar(20),
[厂商05()] varchar(20),
[厂商06()] varchar(20),
[厂商07()] varchar(20),
[厂商08()] varchar(20),
[厂商09()] varchar(20),
[厂商10()] varchar(20),
[厂商11()] varchar(20),
[厂商12()] varchar(20),
[厂商13()] varchar(20),
[厂商14()] varchar(20),
[厂商15()] varchar(20),
[厂商16()] varchar(20),
[厂商17()] varchar(20),
[厂商18()] varchar(20),
[厂商19()] varchar(20),
[厂商20()] varchar(20))
insert into test select '产品广告',1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
insert into test select '形象广告',10,11,12,13,14,1,2,16,17,18,19,20,3,4,5,6,7,8,9,15
insert into test select '其他广告',16,17,18,19,20,8,9,10,11,12,13,1,2,3,4,5,6,7,14,15
gocreate procedure sp_test(@num int)
as
begin
create table #t1(Company varchar(20),AdType varchar(20),Score int)
create table #t2(Company varchar(20),AdType varchar(20),Score int)
declare @s varchar(8000)
set @s=''
select @s=@s+' union all select '''+name+''',AdType,['+name+'] from test' from syscolumns where id=object_id('test') and colid>1
set @s='insert into #t1(Company,AdType,Score)'+stuff(@s,1,10,'')
exec(@s)
set @s='insert into #t2 select a.* from #t1 a where a.Company in(select top '+rtrim(@num)+' Company from #t1 where AdType=a.AdType order by Score desc)'
exec(@s)
set @s='select AdType'
select @s=@s+',['+Company+']=max(case Company when '''+Company+''' then Score else 0 end)' from #t2 group by Company
set @s=@s+' from #t2 group by AdType'
exec(@s)
end
goexec sp_test 3
/*
AdType 厂商03() 厂商04() 厂商05() 厂商10() 厂商11() 厂商12() 厂商18() 厂商19() 厂商20()
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
产品广告 0 0 0 0 0 0 18 19 20
其他广告 18 19 20 0 0 0 0 0 0
形象广告 0 0 0 18 19 20 0 0 0
*/
godrop procedure sp_test
drop table test
as
begin
declare @sql varchar(8000)
create table #(Company varchar(20),AdType varchar(20),Score int)
set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)'
exec(@sql)
set @sql='select AdType'
select @sql=@sql+',['+Company+']=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company
set @sql=@sql+' from # group by AdType'
exec(@sql)
end
go
as
begin
declare @sql varchar(8000)
create table #(Company varchar(20),AdType varchar(20),Score int)
set @sql='insert into # select a.* from test a where a.Company in(select top '+rtrim(@num)+' Company from test where AdType=a.AdType order by Score desc)'
exec(@sql)
set @sql='select AdType'
select @sql=@sql+',['+Company+']=max(case Company when '''+Company+''' then Score else 0 end)' from # group by Company
set @sql=@sql+' from test group by AdType'
exec(@sql)
end
go