现在已经通过sql语句查询到如下的数据：
xm sfzh xn je flag
李晓婷 5157528 2011 10250.0 1
李晓婷 5157528 2012 9500.0 1
李晓婷 5157528 2013 9500.0 1
flag值1为已收，-1为欠费
sf1为2011收费，qf1为2011学年欠费
sf2为2012收费，qf2为2012学年欠费
sf3为2013收费，qf3为2013学年欠费
如何在这个查询的结果的基础上显示如下格式：
xm sfzh sf1 qf1 sf2 qf2 sf3 qf3
李晓婷 5157528 10250.0 0 9500.0 0 9500.0 0
------解决思路----------------------
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
------解决思路----------------------
根据楼主说的是sql server 2000，这样#1方法就有问题，#1的方法适合在sql server 2005及以上版本使用。
但是#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