紧急求助！！这样的存储过程如何编制！！

table1
--------------------------------------
_date   e_name  
09-03   A
09-03   B
09-03   C
09-04   A
09-04   B
09-04   D
09-04   E
09-05   B
09-05   E
-------------------------------------
table2
_date   A   B   C   D   E
09-03   1   1   1   0   0  
09-04   1   1   0   1   1  
09-05   0   1   0   0   1  

0代表‘没有’，1代表‘有’
--------------------------------
小弟   求在table1的基础上统计成table2样式的表,那么生成table2
的存储过程应如何编写？？？？？


------解决方案--------------------
declare @t table(_date varchar(10),name varchar(10))
insert into @t select '09-03 ', 'A '
union all select '09-03 ', 'B '
union all select '09-03 ', 'C '
union all select '09-04 ', 'A '
union all select '09-04 ', 'B '
union all select '09-04 ', 'D '
union all select '09-04 ', 'E '
union all select '09-05 ', 'B '
union all select '09-05 ', 'E '

select * from @t
pivot
(COUNT(name)
for name in ([A],[B],[C],[D],[E])
)as pt

/*
_date A B C D E
---------- ----------- ----------- ----------- ----------- -----------
09-03 1 1 1 0 0
09-04 1 1 0 1 1
09-05 0 1 0 0 1

(3 行受影响)
*/
------解决方案--------------------
没有测试

select _date,case when ac> 0 then 1 else 0 end as 'A ',
case when bc> 0 then 1 else 0 end as 'B ',
case when cc> 0 then 1 else 0 end as 'C ',
case when dc> 0 then 1 else 0 end as 'D ',
case when ec> 0 then 1 else 0 end as 'E '
from
(
select _date,
SUM(case when e_name= 'A ' then 1 else 0 end) as ac,
SUM(case when e_name= 'B ' then 1 else 0 end) as bc,
SUM(case when e_name= 'C ' then 1 else 0 end) as cc,
SUM(case when e_name= 'D ' then 1 else 0 end) as dc,
SUM(case when e_name= 'E ' then 1 else 0 end) as ec
from table1
group by _date
)temp


------解决方案--------------------
SELECT _DATE,
A=ISNULL(SUM(A),0),
B=ISNULL(SUM(B),0),
C=ISNULL(SUM(C),0),
D=ISNULL(SUM(D),0),
E=ISNULL(SUM(E),0)
FROM ( SELECT _date,
A=case e_name when 'A ' then 1 ELSE 0 end,
B=case e_name when 'B ' then 1 ELSE 0 end,
C=case e_name when 'C ' then 1 ELSE 0 end,
D=case e_name when 'D ' then 1 ELSE 0 end,
E=case e_name when 'E ' then 1 ELSE 0 end
from table1) AS TMP
GROUP BY TMP._DATE
------解决方案--------------------
szwwww() ( ) 信誉：100 Blog 2007-03-23 14:51:39 得分: 0


2,3,4楼的朋友，谢谢你们，你们写的均能通过！！

但是如果e_name的可能值时不确定的，又应该如何做？？？

通过后给分


------------

那就要用動態SQL語句


Declare @S Varchar(8000)
Select @S = 'Select _date '
Select @S = @S + ', Max(Case e_name When ' ' ' + e_name + ' ' ' Then 1 Else 0 End) As ' + e_name