34,590
社区成员
发帖
与我相关
我的任务
分享
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 location, sum(case when state = '在架' then 1 else 0 end) as '在架',
sum(case when state = '報廢' then 1 else 0 end) as '報廢',
sum(case when state = '出倉' then 1 else 0 end) as '出倉',
sum(1) as '小計' from Test group by location
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
*/
if object_id('TEST') is not null drop table TEST
create table TEST
(location varchar(15),
state varchar(5),
product varchar(150)
)
--有兩種三種狀態 在架 報廢 出倉
insert into TEST
select '01-01-01','在架','01'
union select '01-01-01','在架','02'
union select '01-01-01','報廢','S3'
union select '01-01-02','在架','PP'
union select '01-01-02','在架','SS'
union select '01-01-02','出倉','GG'
select * from Test
位置 在架 報廢 出倉 小計
01-01-01 2 1 0 3
01-01-02 2 0 1 3
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')
go
declare @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
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
--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 行受影响)
**/