原表名称 | 日期 |
A 2009-12-01
A 2009-12-02
B 2009-12-01
B 2009-08-01
C 2009-12-01
C 2010-01-01
A 2009-12-02
C 2009-12-01根据原表需要跑出的表名称 | 12月| Total
A 3 3
B 1 2
C 2 1请帮忙看一下,谢谢!
A 2009-12-01
A 2009-12-02
B 2009-12-01
B 2009-08-01
C 2009-12-01
C 2010-01-01
A 2009-12-02
C 2009-12-01根据原表需要跑出的表名称 | 12月| Total
A 3 3
B 1 2
C 2 1请帮忙看一下,谢谢!
from T
group by 名称
名称 | 12月| Total
A 3 3
B 1 2
C 2 3
--> 测试数据:@tb
declare @tb table([名称] varchar(1),[日期] datetime)
insert @tb
select 'A','2009-12-01' union all
select 'A','2009-12-02' union all
select 'B','2009-12-01' union all
select 'B','2009-08-01' union all
select 'C','2009-12-01' union all
select 'C','2010-01-01' union all
select 'A','2009-12-02' union all
select 'C','2009-12-01'select 名称,sum(case when convert(char(07),日期,120)='2009-12' then 1 else 0 end ) as [200912月],
total=count(*)
from @tb
group by 名称
/*
名称 200912月 total
---- ----------- -----------
A 3 3
B 1 2
C 2 3(3 行受影响)
*/
select * from Table_Test
declare @sql varchar(8000)
declare @tb table([Month] varchar(7),[Date] datetime)
insert @tb
select '2009-12','2009-12-01' union all
select '2009-11','2009-11-01' union all
select '2009-10','2009-10-01'
select * from @tb
set @sql ='select MediaName'
select @sql=@sql+',sum(case when convert(char(07),Date_up,120)='''+Month
+''' then 1 else 0 end )['+ Month
+']' from( select Month from @tb) as a
set @sql =@sql+',total=count(*)'
set @sql =@sql+' from Table_Test group by MediaName'
print @sql
exec (@sql)