分步骤实现,先产生含365天的日期表,select convert(varchar,dateadd(d,number,'2014-01-01'),23) 'dates' from master.dbo.spt_values where type='P' and number between 0 and datediff(d,'2014-01-01',dateadd(d,-1,dateadd(yy,1,'2014-01-01')))/* dates ------------------------------ 2014-01-01 2014-01-02 2014-01-03 2014-01-04 2014-01-05 2014-01-06 2014-01-07 2014-01-08 2014-01-09 . . . 2014-12-24 2014-12-25 2014-12-26 2014-12-27 2014-12-28 2014-12-29 2014-12-30 2014-12-31(365 row(s) affected) */
然后用动态SQL进行汇总统计各字段的值..
--建立测试环境 create table T1 (date datetime, customer varchar(10), qty int) insert into T1 select '2014/1/1','A',10 insert into T1 select '2014/1/1','B',100 insert into T1 select '2014/1/1','C',1000 insert into T1 select '2014/1/2','B',3300 insert into T1 select '2014/1/2','C',10create table T2 (customer varchar(10)) insert into T2 select 'A' insert into T2 select 'B' insert into T2 select 'C' insert into T2 select 'D' GO--建立测试usp create proc usp_test @year int as--建立临时表,存储一年日期 if object_id('tempdb..#date') is not null drop table #date
create table #date(date datetime) insert into #date select top 370 dateadd(day, number, convert(datetime,rtrim(@year)+'/1/1')) from master..spt_values where number between 0 and 370 and year(dateadd(day, number, convert(datetime,rtrim(@year)+'/1/1'))) = @year group by number
--建立动态客户字串,按输入年份的sum(qty)倒序 declare @customer varchar(max) set @customer =''select @customer = @customer +',['+customer+']' from ( select T2.customer,sum(isnull(T1.qty,0)) as qty from T2 left join T1 on T2.customer=T1.customer and year([date]) = @year group by T2.customer ) x order by qty descset @customer = stuff(@customer,1,1,'')--建立动态sql语句 declare @sql varchar(max) set @sql=''select @sql = 'select [date] ,'+@customer + ' from T1 as A'+ ' pivot (sum(qty) for customer in('+@customer+')) B' select @sql = 'select x1.[date],total=isnull((select sum(qty) from T1 where [date]=x1.[date]),0) ,'+@customer+ ' from #date as x1 '+ ' left join ('+@sql+') as x2'+ ' on x1.[date]=x2.[date]'+ ' order by 1'
按照playwarcraft大师的代码,小弟依葫芦画瓢,套在程序中,不知道为何,仍有错误。 大师们,请再给与指点:--建立测试usp create proc usp_test @year int as--建立临时表,存储一年日期 if object_id('tempdb..#date') is not null drop table #date
create table #date(date date) insert into #date select top 370 dateadd(day, number, convert(date,rtrim(@year)+'/1/1')) from master..spt_values where number between 0 and 370 and year(dateadd(day, number, convert(datetime,rtrim(@year)+'/1/1'))) = @year group by number
--建立动态客户字串,按输入年份的sum(qty)倒序 declare @customer varchar(max) set @customer =''select @customer = @customer +',['+客户+']' from ( select 客户汇总对比.客户,sum(isnull(Source.[金额(美金)],0)) as [金额(美金)] from 客户汇总对比 left join Source on 客户汇总对比.客户=Source.客户名称 and year([日期]) = @year group by 客户汇总对比.客户 ) x order by [金额(美金)] descset @customer = stuff(@customer,1,1,'')--建立动态sql语句 declare @sql varchar(max) set @sql=''select @sql = 'select 日期 ,'+@customer + ' from Source as A'+ ' pivot (sum([金额(美金)]) for 客户名称 in('+@customer+')) B' select @sql = 'select x1.[date],total=isnull((select sum([金额(美金)]) from Source where [日期]=x1.[date]),0) ,'+@customer+ ' from #date as x1 '+ ' left join ('+@sql+') as x2'+ ' on x1.[date]=x2.[日期]'+ ' order by 1'
from master.dbo.spt_values
where type='P' and number between 0
and datediff(d,'2014-01-01',dateadd(d,-1,dateadd(yy,1,'2014-01-01')))/*
dates
------------------------------
2014-01-01
2014-01-02
2014-01-03
2014-01-04
2014-01-05
2014-01-06
2014-01-07
2014-01-08
2014-01-09
.
.
.
2014-12-24
2014-12-25
2014-12-26
2014-12-27
2014-12-28
2014-12-29
2014-12-30
2014-12-31(365 row(s) affected)
*/
--建立测试环境
create table T1 (date datetime, customer varchar(10), qty int)
insert into T1 select '2014/1/1','A',10
insert into T1 select '2014/1/1','B',100
insert into T1 select '2014/1/1','C',1000
insert into T1 select '2014/1/2','B',3300
insert into T1 select '2014/1/2','C',10create table T2 (customer varchar(10))
insert into T2 select 'A'
insert into T2 select 'B'
insert into T2 select 'C'
insert into T2 select 'D'
GO--建立测试usp
create proc usp_test
@year int
as--建立临时表,存储一年日期
if object_id('tempdb..#date') is not null
drop table #date
create table #date(date datetime)
insert into #date select top 370 dateadd(day, number, convert(datetime,rtrim(@year)+'/1/1'))
from master..spt_values
where number between 0 and 370
and year(dateadd(day, number, convert(datetime,rtrim(@year)+'/1/1'))) = @year
group by number
--建立动态客户字串,按输入年份的sum(qty)倒序
declare @customer varchar(max)
set @customer =''select @customer = @customer +',['+customer+']'
from
( select T2.customer,sum(isnull(T1.qty,0)) as qty
from T2 left join T1
on T2.customer=T1.customer
and year([date]) = @year
group by T2.customer
) x
order by qty descset @customer = stuff(@customer,1,1,'')--建立动态sql语句
declare @sql varchar(max)
set @sql=''select @sql = 'select [date] ,'+@customer +
' from T1 as A'+
' pivot (sum(qty) for customer in('+@customer+')) B'
select @sql = 'select x1.[date],total=isnull((select sum(qty) from T1 where [date]=x1.[date]),0) ,'+@customer+
' from #date as x1 '+
' left join ('+@sql+') as x2'+
' on x1.[date]=x2.[date]'+
' order by 1'
--执行语句
exec (@sql)
GO--查看测试结果
exec usp_test 2014
GO
/**
date total B C A D
----------------------- ----------- ----------- ----------- ----------- -----------
2014-01-01 00:00:00.000 1110 100 1000 10 NULL
2014-01-02 00:00:00.000 3310 3300 10 NULL NULL
2014-01-03 00:00:00.000 0 NULL NULL NULL NULL
2014-01-04 00:00:00.000 0 NULL NULL NULL NULL
2014-01-05 00:00:00.000 0 NULL NULL NULL NULL
2014-01-06 00:00:00.000 0 NULL NULL NULL NULL
2014-01-07 00:00:00.000 0 NULL NULL NULL NULL
2014-01-08 00:00:00.000 0 NULL NULL NULL NULL
2014-01-09 00:00:00.000 0 NULL NULL NULL NULL
2014-01-10 00:00:00.000 0 NULL NULL NULL NULL
2014-01-11 00:00:00.000 0 NULL NULL NULL NULL**/--删除测试环境
drop proc usp_test
drop table T1,T2
总该有个客户端或者web应用吧,至少先保证excel的数据都在 sql表里 。不知道您操作数据是在excel里面,还是在sqlserver 表里面操作。我觉得这个是个前提。
可以分多次填充的啊。先查询客户,按金额降序,从左向右循环列
列头填客户名
查询客户的全年明细,填在该列的数据区
在知道总客户数的情况下,每日总金额的公式不难吧。
create proc usp_test
@year int
as--建立临时表,存储一年日期
if object_id('tempdb..#date') is not null
drop table #date
create table #date(date date)
insert into #date select top 370 dateadd(day, number, convert(date,rtrim(@year)+'/1/1'))
from master..spt_values
where number between 0 and 370
and year(dateadd(day, number, convert(datetime,rtrim(@year)+'/1/1'))) = @year
group by number
--建立动态客户字串,按输入年份的sum(qty)倒序
declare @customer varchar(max)
set @customer =''select @customer = @customer +',['+客户+']'
from
( select 客户汇总对比.客户,sum(isnull(Source.[金额(美金)],0)) as [金额(美金)]
from 客户汇总对比 left join Source
on 客户汇总对比.客户=Source.客户名称
and year([日期]) = @year
group by 客户汇总对比.客户
) x
order by [金额(美金)] descset @customer = stuff(@customer,1,1,'')--建立动态sql语句
declare @sql varchar(max)
set @sql=''select @sql = 'select 日期 ,'+@customer +
' from Source as A'+
' pivot (sum([金额(美金)]) for 客户名称 in('+@customer+')) B'
select @sql = 'select x1.[date],total=isnull((select sum([金额(美金)]) from Source where [日期]=x1.[date]),0) ,'+@customer+
' from #date as x1 '+
' left join ('+@sql+') as x2'+
' on x1.[date]=x2.[日期]'+
' order by 1'
--执行语句
exec (@sql)
GO--查看测试结果
exec usp_test 2014
GO--删除测试环境
drop proc usp_test
结果如下:有两个问题:
1. 大量日期重复的情况,如何合并?
2. 表格中大量NULL,如何变为0,请给与详细指导
感激大师们继续给与指点!小弟初学,所知有限,请再给与帮助,小弟祈求。。