求SQL语句一条，，，搞了半天没高出来，谢谢各位大鸟。。。

[wangjunloverqq]

表的脚本是：

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 的个数确定的。
谢谢各位了。

[百年树人]

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 行受影响)
**/

[AcHerat]

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

[王思明]

竟然和我的問題一樣。。。哈哈

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 

[Felixzhaowenzhong]

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
*/

[wangjunloverqq]

[Quote=引用 1 楼 的回复:]

SQL code
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……
[/Quote]
感谢+崇拜，哎，，惭愧 自己业务不精，要学的东西太多了。。
刚才另外一个帖子本来要跟分给你的，结贴时 点错人了，你下面几个的回复和你的是一样的，回复给他们了。。。抱歉。。。。

[王思明]

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

[王思明]

樓主，要給辛苦分呀！我都是自己寫的