JBTALKS.CC

标题: 帮帮忙！！MSSQL pivot( ) [打印本页]

作者: purpleEve 时间: 2012-7-31 10:47 AM
标题: 帮帮忙！！MSSQL pivot( )
result:

castid S1 S2 S3 S4 S5 S6
------ -- -- -- -- -- --
13700 1 1 1 1 1 1
13701 1 1 1 1 1 1
13702 1 1 1 1 1 1
13703 1 1 1 1 1 1
13704 1 1 1 1 1 1
13705 1 1 1 1 1 1
13706 1 1 1 1 1 1
13707 1 1 1 0 1 1
13708 1 0 1 0 1 1

syntax :
------------
select c.castid,
sum(CASE WHEN strandid = 1 AND s.castend - s.caststart >0 THEN 1 ELSE 0 END) as S1,
sum(CASE WHEN strandid = 2 AND s.castend - s.caststart >0 THEN 1 ELSE 0 END) as S2,
sum(CASE WHEN strandid = 3 AND s.castend - s.caststart >0 THEN 1 ELSE 0 END) as S3,
sum(CASE WHEN strandid = 4 AND s.castend - s.caststart >0 THEN 1 ELSE 0 END) as S4,
sum(CASE WHEN strandid = 5 AND s.castend - s.caststart >0 THEN 1 ELSE 0 END) as S5,
sum(CASE WHEN strandid = 6 AND s.castend - s.caststart >0 THEN 1 ELSE 0 END) as S6
from tbcast c JOIN tbstrand s ON c.castid = s.castid
group by c.castid

以上是用case()来做.
如果用pivot()来做你们有谁会吗?

欢迎光临 JBTALKS.CC (https://jbtalks.my/)