交叉报表

select * from bb

create table bb (
 单位名称 varchar(10),
 日期 datetime,
 销售额 int
)

insert into bb(单位名称,日期,销售额)
select 'A单位','2008-01-10','100'
union all
select 'B单位','2008-01-09','100'
union all
select 'C单位','2008-01-08','200'
union all
select 'D单位','2008-01-06','300'
union all
select 'E单位','2008-01-07','250'
union all
select 'F单位','2008-01-05','200'
union all
select 'G单位','2008-01-04','350'

declare @sql varchar(1000)
set @sql='select 日期=convert(varchar(10),日期,120)'
select @sql=@sql+',['+单位名称
+']=sum(case 单位名称 when '''+单位名称+'''THEN 销售额 ELSE 0 END)'
FROM(SELECT DISTINCT 单位名称 FROM bb)a
exec(@sql+'FROM bb GROUP BY CONVERT(VARCHAR(10),日期,120)')

 

declare @sql varchar(1000)
set @sql='select 日期=convert(varchar(10),日期,120)'
select @sql=@sql+',['+单位名称
+']=sum(case 单位名称 when '''+单位名称+'''THEN 销售额 ELSE 0 END)'
FROM(SELECT DISTINCT 单位名称 FROM bb)a
exec(@sql+'FROM bb GROUP BY CONVERT(VARCHAR(10),日期,120)')