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
的存储过程应如何编写？？？？？
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 行受影响)
*/
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
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
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
From table1 Group By e_name
Select @S = @S + ' From table1 Group By _date'
EXEC(@S)
谢谢你
马上给分！还有一个小问题，我是个新手，对于你给的这段程序。测试通过了，可是我看不懂，能否给我解释一下/Select @S = @S + ', Max(Case e_name When ''' + e_name + ''' Then 1 Else 0 End) As ' + e_name/这句是什么意思？