表A 有三个字段 DATE datetime ---日期
MAN varchar(20) --员工
COUNT int --数量报表的要求是: DATE MAN1 MAN2 MAN3 MAN4
2011-5-1 COUNT1(5月1日这天MAN1的数量) COUNT COUNT ....
2011-5-2 COUNT
2011-5-3 .....
2011-5-4
2011-5-5 第一列 日期列:没有不现实,有就显示一天
第二列 至最后一列:当天有多少人 就显示多少列,(去掉重复)
COUNT :2011-5-1 这天 MAN1 干了多少COUNT(汇总) 谢谢各位了,
MAN varchar(20) --员工
COUNT int --数量报表的要求是: DATE MAN1 MAN2 MAN3 MAN4
2011-5-1 COUNT1(5月1日这天MAN1的数量) COUNT COUNT ....
2011-5-2 COUNT
2011-5-3 .....
2011-5-4
2011-5-5 第一列 日期列:没有不现实,有就显示一天
第二列 至最后一列:当天有多少人 就显示多少列,(去掉重复)
COUNT :2011-5-1 这天 MAN1 干了多少COUNT(汇总) 谢谢各位了,
select
@sql=isnull(@sql+',','')
+'sum(case when MAN='''+MAN+' then [COUNT] else 0 end) as ['+MAN+']'
from
(select distinct MAN from a) texec ('select convert(varchar(10),date,120) as 日期,'+@sql+' from a group by convert(varchar(10),date,120)')
select
@sql=isnull(@sql+',','')
+'sum(case when MAN='''+MAN+''' then [COUNT] else 0 end) as ['+MAN+']'
from
(select distinct MAN from a) t
--print @sql
exec ('select convert(varchar(10),date,120) as 日期,'+@sql+' from a group by convert(varchar(10),date,120)')
可以用print @sql 打印出来执行看看在哪里出错了
小弟我 菜的很,表的脚本是:CREATE TABLE TB1
(
SendMan VARCHAR(20),
SendDate datetime,
SaleOrderCode VARCHAR(20)
)
INSERT INTO TB1 VALUES('小王','2012-5-1','FB001')
INSERT INTO TB1 VALUES('小王','2012-5-1','FB002')
INSERT INTO TB1 VALUES('小王','2012-5-2','FB003')
INSERT INTO TB1 VALUES('小王','2012-5-3','FB004')
INSERT INTO TB1 VALUES('小王','2012-5-3','FB0044')INSERT INTO TB1 VALUES('小李','2012-5-1','FB005')
INSERT INTO TB1 VALUES('小李','2012-5-2','FB006')
INSERT INTO TB1 VALUES('小李','2012-5-3','FB007')
INSERT INTO TB1 VALUES('小李','2012-5-4','FB008')INSERT INTO TB1 VALUES('小张','2012-5-2','FB009')
INSERT INTO TB1 VALUES('小张','2012-5-3','FB0010')
INSERT INTO TB1 VALUES('小张','2012-5-4','FB0011')
INSERT INTO TB1 VALUES('小张','2012-5-5','FB0012')我要的结果是 类似下面图片这样的。其中 数量 是根据 SaleOrderCode 的个数确定的。
谢谢各位了。
select
@sql=isnull(@sql+',','')
+'sum(case when SendMan='''+SendMan+''' then 1 else 0 end) as ['+SendMan+']'
from
(select distinct SendMan from tb1) t
--print @sql
exec ('select convert(varchar(10),SendDate,120) as 日期,'+@sql+' from tb1 group by convert(varchar(10),SendDate,120)')/**
日期 小李 小王 小张
---------- ----------- ----------- -----------
2012-05-01 1 2 0
2012-05-02 1 1 1
2012-05-03 1 2 1
2012-05-04 1 0 1
2012-05-05 0 0 1(5 行受影响)
**/
CREATE TABLE TB1
(
SendMan VARCHAR(20),
SendDate datetime,
SaleOrderCode VARCHAR(20)
)
INSERT INTO TB1 VALUES('小王','2012-5-1','FB001')
INSERT INTO TB1 VALUES('小王','2012-5-1','FB002')
INSERT INTO TB1 VALUES('小王','2012-5-2','FB003')
INSERT INTO TB1 VALUES('小王','2012-5-3','FB004')
INSERT INTO TB1 VALUES('小王','2012-5-3','FB0044')INSERT INTO TB1 VALUES('小李','2012-5-1','FB005')
INSERT INTO TB1 VALUES('小李','2012-5-2','FB006')
INSERT INTO TB1 VALUES('小李','2012-5-3','FB007')
INSERT INTO TB1 VALUES('小李','2012-5-4','FB008')INSERT INTO TB1 VALUES('小张','2012-5-2','FB009')
INSERT INTO TB1 VALUES('小张','2012-5-3','FB0010')
INSERT INTO TB1 VALUES('小张','2012-5-4','FB0011')
INSERT INTO TB1 VALUES('小张','2012-5-5','FB0012')
godeclare @sql varchar(8000)
set @sql = 'select convert(varchar(10),senddate,120) as date'
select @sql = @sql+',sum(case sendman when '''+sendman+''' then 1 else 0 end) as ['+sendman+']'
from tb1 group by sendman
select @sql = @sql + ' from tb1 group by convert(varchar(10),senddate,120)'
exec(@sql)drop table tb1/**********************************date 小李 小王 小张
---------- ----------- ----------- -----------
2012-05-01 1 2 0
2012-05-02 1 1 1
2012-05-03 1 2 1
2012-05-04 1 0 1
2012-05-05 0 0 1(5 行受影响)
CREATE TABLE TB1
(
SendMan VARCHAR(20),
SendDate datetime,
SaleOrderCode VARCHAR(20)
)
INSERT INTO TB1 VALUES('小王','2012-5-1','FB001')
INSERT INTO TB1 VALUES('小王','2012-5-1','FB002')
INSERT INTO TB1 VALUES('小王','2012-5-2','FB003')
INSERT INTO TB1 VALUES('小王','2012-5-3','FB004')
INSERT INTO TB1 VALUES('小王','2012-5-3','FB0044')INSERT INTO TB1 VALUES('小李','2012-5-1','FB005')
INSERT INTO TB1 VALUES('小李','2012-5-2','FB006')
INSERT INTO TB1 VALUES('小李','2012-5-3','FB007')
INSERT INTO TB1 VALUES('小李','2012-5-4','FB008')INSERT INTO TB1 VALUES('小张','2012-5-2','FB009')
INSERT INTO TB1 VALUES('小张','2012-5-3','FB0010')
INSERT INTO TB1 VALUES('小张','2012-5-4','FB0011')
INSERT INTO TB1 VALUES('小张','2012-5-5','FB0012')
select * from TB1
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'sum(case when SendMan='''+SendMan+''' then 1 else 0 end) as ['+SendMan+']'
from
(select distinct SendMan from TB1 ) t
exec ('select convert(varchar(10),SendDate,120) as 日期,'+@sql+' from TB1 group by convert(varchar(10),SendDate,120)')
/*
日期 小李 小王 小张
2012-05-01 1 2 0
2012-05-02 1 1 1
2012-05-03 1 2 1
2012-05-04 1 0 1
2012-05-05 0 0 1
*/