原始数据有ID,Name,Total1,Total2,Date1字段，如下面显示
ID Name Total1 Total2 Date1
1 Name1 8.981132 476 6/15/2014
1 Name2 9 18 7/9/2014
1 Name3 14.589062 1203.16 7/25/2014
1 Name4 30.750759 2023.4 7/11/2014
2 Name1 7 7 6/13/2014
2 Name2 13.491984 5714.8 7/22/2014
2 Name2 26.976811 5921.68 7/18/2014
期望获取到每个ID，每个Name在每个月的上中下旬的数据统计
ID Name 6-上旬-Total1 6-上旬-Total2 6-中旬-Total1 6-中旬-Total2 6-下旬-Total1 6-下旬-Total2 7-上旬-Total1 7-上旬-Total2 7-中旬-Total1 7-中旬-Total2 7-下旬-Total1 7-下旬-Total2
1 Name1 0 0 8.981132 476 0 0 0 0 0 0 0 0
1 Name2 0 0 0 0 0 9 18 0 0 0 0
1 Name3 14.589062 1203.16
1 Name4 30.750759 2023.4
2 Name1 7 7
2 Name2 13.491984 5714.8
2 Name2 26.976811 5921.68
------解决思路----------------------
DECLARE @MONTH DATETIME感觉只能这样了
SET @MONTH='2014-07-01'
SELECT ID,Name
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [preSelectedMonth-f-Total1]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [preSelectedMonth-f-Total2]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [preSelectedMonth-m-Total1]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [preSelectedMonth-m-Total2]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [preSelectedMonth-l-Total1]
,CASE WHEN MONTH(DATEADD(MONTH,1,Date1))=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [preSelectedMonth-l-Total2]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total1 END [SelectedMonth-f-Total1]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=10 THEN Total2 END [SelectedMonth-f-Total2]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total1 END [SelectedMonth-m-Total1]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)<=20 AND DATEPART(DAY,Date1)>10 THEN Total2 END [SelectedMonth-m-Total2]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total1 END [SelectedMonth-l-Total1]
,CASE WHEN MONTH(Date1)=MONTH(@MONTH)AND DATEPART(DAY,Date1)>20 THEN Total2 END [SelectedMonth-l-Total2]
FROM TB
------解决思路----------------------
use tempdb
create table test(id int,[name] varchar(30),total1 decimal(18,6),total2 decimal(18,6),date1 smalldatetime)
insert into test
select 1, 'Name1', 8.981132, 476 ,'6/15/2014' union all
select 1, 'Name2', 9 , 18 ,'7/9/2014' union all
select 1, 'Name3', 14.589062, 1203.16 ,'7/25/2014' union all
select 1, 'Name4', 30.750759, 2023.4 ,'7/11/2014' union all
select 2, 'Name1', 7 , 7 ,'6/13/2014' union all
select 2, 'Name2', 13.491984, 5714.8 ,'7/22/2014' union all
select 2, 'Name2', 26.976811, 5921.68 ,'7/18/2014'
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+' sum(case when day(date1) between 1 and 10 then total1 else 0 end) as ['+ cast(month(date1) as varchar)+'-上旬-total1],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total1 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total1],'+char(10)+
' sum(case when day(date1)>20 then total1 else 0 end) as [' +cast(month(date1) as varchar)+'-下旬-total1],'+char(10)+
' sum(case when day(date1) between 1 and 10 then total2 else 0 end) as ['+ cast(month(date1) as varchar)+'-上旬-total2],'+char(10)+
' sum(case when day(date1)>10 and day(date1)<=20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'-中旬-total2],'+char(10)+
' sum(case when day(date1)>20 then total2 else 0 end ) as ['+ cast(month(date1) as varchar)+'下旬-total2]'
from test group by month(date1)
set @sql='select id,[name],'+@sql
+'from test group by id,name,convert(varchar(6),date1,112)'
--print @sql
exec(@sql)