DECLARE @sql varchar(8000)
SET @sql = 'select dic'SELECT @sql =@sql+',sum (case when zhanqu=''' + zhanqu + ''' and 期数=1
then tanweishunum else 0 end) as ['+ zhanqu + '1期],sum (case when zhanqu='''+ zhanqu + ''' and 期数=2 then tanweishunum else 0 end) ['+ zhanqu + '2期]'
FROM (SELECT DISTINCT zhanqu
FROM department) a
SELECT @sql = @sql + ' from department group by dic' print @sql
exec(@sql)
SET @sql = 'select dic'SELECT @sql =@sql+',sum (case when zhanqu=''' + zhanqu + ''' and 期数=1
then tanweishunum else 0 end) as ['+ zhanqu + '1期],sum (case when zhanqu='''+ zhanqu + ''' and 期数=2 then tanweishunum else 0 end) ['+ zhanqu + '2期]'
FROM (SELECT DISTINCT zhanqu
FROM department) a
SELECT @sql = @sql + ' from department group by dic' print @sql
exec(@sql)
SET @sql = 'select dic'SELECT @sql =@sql+',sum (case when zhanqu=''' + zhanqu + ''' and 期数='+cast(期数 as varchar(10))+' then tanweishunum else 0 end) ['+ zhanqu+cast(期数 as varchar(10))+'期(摊位数)],sum (case when zhanqu=''' + zhanqu + ''' and 期数='+cast(期数 as varchar(10))+' then 面积 else 0 end) ['+ zhanqu+cast(期数 as varchar(10))+'期(面积)]' FROM (SELECT DISTINCT zhanqu,期数 FROM department) a SELECT @sql = @sql + ' from department group by dic' print @sql
exec(@sql)
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as aselect @sql = left(@sql,len(@sql)-1) + ' from #'exec(@sql)drop table #
create table test(地区 varchar(100),类型 varchar(100),期数 int,摊位数 int,面积 int)
insert into test
select '烟台','其他',1,1,20
union
select '烟台','按面积',2,2,5
union
select '青岛','其他',1,2,8
union
select '济南','按面积',2,1,6
union
select '青岛','其他',2,1,7
DECLARE @sql varchar(8000)
SET @sql = 'select 地区'
SELECT @sql =@sql+',sum (case when cast(摊位数 as varchar(10))=' + cast(摊位数 as varchar(10)) + ' and cast(期数 as varchar(10))='+cast(期数 as varchar(10))+' then 面积 else 0 end) ['+cast(摊位数 as varchar(10))+'摊位'+cast(期数 as varchar(10))+'期数]'
FROM (SELECT DISTINCT 摊位数,期数 FROM test) a
SELECT @sql = @sql + ' from test group by 地区'
exec(@sql)
drop table test
create table test(地区 varchar(100),类型 varchar(100),期数 int,摊位数 int,面积 int)
insert into test
select '烟台','其他',1,1,20
union
select '烟台','按面积',2,2,5
union
select '青岛','其他',1,2,8
union
select '济南','按面积',2,1,6
union
select '青岛','其他',2,1,7
DECLARE @sql varchar(8000)
SET @sql = 'select 地区'
SELECT @sql =@sql+',sum (case when cast(摊位数 as varchar(10))=' + cast(摊位数 as varchar(10)) + ' and cast(期数 as varchar(10))='+cast(期数 as varchar(10))+' then 面积 else 0 end) ['+cast(摊位数 as varchar(10))+'摊位'+cast(期数 as varchar(10))+'期数]'
FROM (SELECT DISTINCT 摊位数,期数 FROM test) a
SELECT @sql = @sql + ' from test group by 地区'
exec(@sql)
drop table test
insert into test
select '烟台','其他',1,1,20
union
select '烟台','按面积',2,2,5
union
select '青岛','其他',1,2,8
union
select '济南','按面积',2,1,6
union
select '青岛','其他',2,1,7select 类型,sum(摊位数) as 摊位数,sum(面积) as 面积 from test group by 类型
drop table test