select a.*,ISNULL(qf1,0) as qf1,ISNULL(qf2,0) as qf2,ISNULL(qf3,0) as qf3 from
(
SELECT xm,sfzh,[2011] as sf1,[2012] as sf2,[2013] as sf3
from (select * FROM [master].[dbo].[Table_1] where flag=1) t
pivot (sum(je) for xn in([2011],[2012],[2013]))
as pvt
) a
left join
(
SELECT xm,sfzh,[2011] as qf1,[2012] as qf2,[2013] as qf3
from (select * FROM [master].[dbo].[Table_1] where flag=-1) t
pivot (sum(je) for xn in([2011],[2012],[2013]))
as pvt
) b on a.sfzh=b.sfzh
select XM,sfzh,
sum(case when xn=2011 and flag=1 then je else 0 end) as sf1,
sum(case when xn=2011 and flag=0 then je else 0 end) as qf1,
sum(case when xn=2012and flag=1 then je else 0 end) as sf1,
sum(case when xn=2012 and flag=0 then je else 0 end) as qf1,
sum(case when xn=2013 and flag=1 then je else 0 end) as sf1,
sum(case when xn=2013 and flag=0 then je else 0 end) as qf1
from cte
group by xm,sfzh
但是#3的方法是正确的,数据有点问题。select XM,sfzh,
sum(case when xn=2011 and flag=1 then je else 0 end) as sf1,
sum(case when xn=2011 and flag=-1 then je else 0 end) as qf1,
sum(case when xn=2012 and flag=1 then je else 0 end) as sf2,
sum(case when xn=2012 and flag=-1 then je else 0 end) as qf2,
sum(case when xn=2013 and flag=1 then je else 0 end) as sf3,
sum(case when xn=2013 and flag=-1 then je else 0 end) as qf3
from tablenme group by xm,sfzh
http://bbs.csdn.net/topics/240002706#3的方法必须在sum前面加max,不然代码会提示错误。