我希望能够优化一个查询，因为它的运行时间过长（大约1.5小时）。通过查看执行计划，发现其中最耗时的操作之一是将主查询结果插入到临时表中。当我把数据插入到临时表中时，会有以下流程：
流程（成本%）
Compute Scalar (0%) <-- Stream Aggregate (0%) <-- Sort(0%) <-- Table Spool (eager spool) (10%)
这很有道理，但是让我感到困惑的是为什么会出现9次？
如果需要，我可以包含查询/索引信息，但是查询本身非常长，我不想把这个问题变成一篇短文！
谢谢， Davin。
编辑以包括查询 - 对于长度表示抱歉！
IF object_id('tempdb..#category') IS NOT NULL DROP TABLE #category
CREATE TABLE #category
(
contact_id NVARCHAR(255)
,donor_class NVARCHAR(255)
,payment_type NVARCHAR(255)
,donation_type NVARCHAR(255)
,approach_channel NVARCHAR(255)
,count1987 INT
,sum1987 DECIMAL(18,3)
,count1988 INT
,sum1988 DECIMAL(18,3)
,count1989 INT
,sum1989 DECIMAL(18,3)
,count1990 INT
,sum1990 DECIMAL(18,3)
,count1991 INT
,sum1991 DECIMAL(18,3)
,count1992 INT
,sum1992 DECIMAL(18,3)
,count1993 INT
,sum1993 DECIMAL(18,3)
,count1994 INT
,sum1994 DECIMAL(18,3)
,count1995 INT
,sum1995 DECIMAL(18,3)
,count1996 INT
,sum1996 DECIMAL(18,3)
,count1997 INT
,sum1997 DECIMAL(18,3)
,count1998 INT
,sum1998 DECIMAL(18,3)
,count1999 INT
,sum1999 DECIMAL(18,3)
,count2000 INT
,sum2000 DECIMAL(18,3)
,count2001 INT
,sum2001 DECIMAL(18,3)
,count2002 INT
,sum2002 DECIMAL(18,3)
,count2003 INT
,sum2003 DECIMAL(18,3)
,count2004 INT
,sum2004 DECIMAL(18,3)
,count2005 INT
,sum2005 DECIMAL(18,3)
,count2006 INT
,sum2006 DECIMAL(18,3)
,count2007 INT
,sum2007 DECIMAL(18,3)
,count2008 INT
,sum2008 DECIMAL(18,3)
,count2009 INT
,sum2009 DECIMAL(18,3)
,count2010 INT
,sum2010 DECIMAL(18,3)
,category05 NVARCHAR(255)
,category06 NVARCHAR(255)
,category07 NVARCHAR(255)
,category08 NVARCHAR(255)
,category09 NVARCHAR(255)
,category10 NVARCHAR(255)
,[1987] NVARCHAR(4)
,[1988] NVARCHAR(4)
,[1989] NVARCHAR(4)
,[1990] NVARCHAR(4)
,[1991] NVARCHAR(4)
,[1992] NVARCHAR(4)
,[1993] NVARCHAR(4)
,[1994] NVARCHAR(4)
,[1995] NVARCHAR(4)
,[1996] NVARCHAR(4)
,[1997] NVARCHAR(4)
,[1998] NVARCHAR(4)
,[1999] NVARCHAR(4)
,[2000] NVARCHAR(4)
,[2001] NVARCHAR(4)
,[2002] NVARCHAR(4)
,[2003] NVARCHAR(4)
,[2004] NVARCHAR(4)
,[2005] NVARCHAR(4)
,[2006] NVARCHAR(4)
,[2007] NVARCHAR(4)
,[2008] NVARCHAR(4)
,[2009] NVARCHAR(4)
,[2010] NVARCHAR(4)
)
CREATE INDEX #idx_category ON #category (contact_id)
;WITH complete_finances as
(
(
SELECT
don.supporter_id AS contact_id
,don.id AS gift_id
,YEAR(don.date_received) AS calendar_year
,YEAR(don.date_received) - CASE WHEN MONTH(don.date_received) < 4 THEN 1 ELSE 0 END AS financial_year
,don.date_received AS date_received
,don.event_id AS event_id
,SUM(CASE WHEN don.gift_aid_status <> 4 THEN don.value_gross * ((dfa.percentage) / 100)
WHEN don.gift_aid_status = 4 AND don.value_net > don.value_gross
AND don.value_net <> 0 THEN don.value_net * ((dfa.percentage) / 100)
ELSE don.value_gross * ((dfa.percentage) / 100)
END
) AS donation_value
,(CASE WHEN don.payment_method IN (1, 2, 10, 11, 12, 200000, 200001, 200002, 200003, 200004, 200007, 200008, 200009 --single payments
,7,13,14) -- single payment refunds
THEN 'single'
WHEN don.payment_method IN (3,4,5,9,200006)
THEN 'regular'
ELSE 'other' END) AS type1
,CASE WHEN dfa.fund_id IN --all emergency funds
(
select f.id
FROM _audit a
INNER JOIN _fund f ON a.article_id = f.id
WHERE a.entity_name = 'Fund'
AND a.Changes LIKE
'%finance_code2%OldValue>3%'
UNION
select
id
FROM _fund
WHERE (finance_code2 LIKE '3%'
OR finance_code2 LIKE '9%')
AND finance_code2 IS NOT NULL
) THEN 'Emergency' else 'Non-emergency' end as type2
FROM donation don WITH (nolock)
INNER JOIN donation_fund_allocation dfa WITH (nolock) ON dfa.donation_id = don.id
WHERE don.supporter_id IS NOT NULL
AND don.status = 4 -- posted donations only
AND don.value_gross <> 0 -- to include refunds as well
GROUP BY don.supporter_id
,don.id
,don.date_received
,don.event_id
,don.payment_method
,dfa.fund_id
)
UNION ALL
(
SELECT
caw.contact_id AS contact_id
,caw.id AS gift_id
,YEAR(caw.bank_date) AS calendar_year
,YEAR(caw.bank_date) - CASE WHEN MONTH(caw.bank_date) < 4 THEN 1 ELSE 0 END AS financial_year
,caw.bank_date AS date_received
,caw.event AS event_id
,(CASE WHEN caw.gift_aid_status <> 4 THEN caw.amount_exc_gift_aid
WHEN caw.gift_aid_status = 4 THEN amount_inc_gift_aid
END
) AS donation_value
,'CAW gift' AS type1
,'Non-emergency' AS type2
FROM gifts caw WITH (nolock)
WHERE caw.contact_id IS NOT NULL
AND caw.amount_exc_gift_aid <> 0
)
)
,category AS
(
SELECT
cmf.contact_id AS contact_id
,lfu.description AS donor_class
,ISNULL(cmf.type1,'Total') AS payment_type
,ISNULL(lac.description, 'Total') AS approach_channel
--,ISNULL(CASE WHEN type1 = 'CAW gift' THEN 'CAW Gift'
-- WHEN type1 = 'Single' THEN 'Non CAW gift'
-- WHEN type1 = 'Regular' THEN 'Non CAW gift'
-- WHEN type1 = 'Other' THEN 'Non CAW gift'
-- END,'Total') AS payment_type2
,ISNULL(cmf.type2,'Total') AS donation_type
,SUM(CASE WHEN cmf.financial_year = '1987' THEN 1 ELSE 0 END) AS count1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END) AS sum1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN 1 ELSE 0 END) AS count1988
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END) AS sum1988
,SUM(CASE WHEN cmf.financial_year = '1989' THEN 1 ELSE 0 END) AS count1989
,SUM(CASE WHEN cmf.financial_year = '1989' THEN cmf.donation_value ELSE 0 END) AS sum1989
,SUM(CASE WHEN cmf.financial_year = '1990' THEN 1 ELSE 0 END) AS count1990
,SUM(CASE WHEN cmf.financial_year = '1990' THEN cmf.donation_value ELSE 0 END) AS sum1990
,SUM(CASE WHEN cmf.financial_year = '1991' THEN 1 ELSE 0 END) AS count1991
,SUM(CASE WHEN cmf.financial_year = '1991' THEN cmf.donation_value ELSE 0 END) AS sum1991
,SUM(CASE WHEN cmf.financial_year = '1992' THEN 1 ELSE 0 END) AS count1992
,SUM(CASE WHEN cmf.financial_year = '1992' THEN cmf.donation_value ELSE 0 END) AS sum1992
,SUM(CASE WHEN cmf.financial_year = '1993' THEN 1 ELSE 0 END) AS count1993
,SUM(CASE WHEN cmf.financial_year = '1993' THEN cmf.donation_value ELSE 0 END) AS sum1993
,SUM(CASE WHEN cmf.financial_year = '1994' THEN 1 ELSE 0 END) AS count1994
,SUM(CASE WHEN cmf.financial_year = '1994' THEN cmf.donation_value ELSE 0 END) AS sum1994
,SUM(CASE WHEN cmf.financial_year = '1995' THEN 1 ELSE 0 END) AS count1995
,SUM(CASE WHEN cmf.financial_year = '1995' THEN cmf.donation_value ELSE 0 END) AS sum1995
,SUM(CASE WHEN cmf.financial_year = '1996' THEN 1 ELSE 0 END) AS count1996
,SUM(CASE WHEN cmf.financial_year = '1996' THEN cmf.donation_value ELSE 0 END) AS sum1996
,SUM(CASE WHEN cmf.financial_year = '1997' THEN 1 ELSE 0 END) AS count1997
,SUM(CASE WHEN cmf.financial_year = '1997' THEN cmf.donation_value ELSE 0 END) AS sum1997
,SUM(CASE WHEN cmf.financial_year = '1998' THEN 1 ELSE 0 END) AS count1998
,SUM(CASE WHEN cmf.financial_year = '1998' THEN cmf.donation_value ELSE 0 END) AS sum1998
,SUM(CASE WHEN cmf.financial_year = '1999' THEN 1 ELSE 0 END) AS count1999
,SUM(CASE WHEN cmf.financial_year = '1999' THEN cmf.donation_value ELSE 0 END) AS sum1999
,SUM(CASE WHEN cmf.financial_year = '2000' THEN 1 ELSE 0 END) AS count2000
,SUM(CASE WHEN cmf.financial_year = '2000' THEN cmf.donation_value ELSE 0 END) AS sum2000
,SUM(CASE WHEN cmf.financial_year = '2001' THEN 1 ELSE 0 END) AS count2001
,SUM(CASE WHEN cmf.financial_year = '2001' THEN cmf.donation_value ELSE 0 END) AS sum2001
,SUM(CASE WHEN cmf.financial_year = '2002' THEN 1 ELSE 0 END) AS count2002
,SUM(CASE WHEN cmf.financial_year = '2002' THEN cmf.donation_value ELSE 0 END) AS sum2002
,SUM(CASE WHEN cmf.financial_year = '2003' THEN 1 ELSE 0 END) AS count2003
,SUM(CASE WHEN cmf.financial_year = '2003' THEN cmf.donation_value ELSE 0 END) AS sum2003
,SUM(CASE WHEN cmf.financial_year = '2004' THEN 1 ELSE 0 END) AS count2004
,SUM(CASE WHEN cmf.financial_year = '2004' THEN cmf.donation_value ELSE 0 END) AS sum2004
,SUM(CASE WHEN cmf.financial_year = '2005' THEN 1 ELSE 0 END) AS count2005
,SUM(CASE WHEN cmf.financial_year = '2005' THEN cmf.donation_value ELSE 0 END) AS sum2005
,SUM(CASE WHEN cmf.financial_year = '2006' THEN 1 ELSE 0 END) AS count2006
,SUM(CASE WHEN cmf.financial_year = '2006' THEN cmf.donation_value ELSE 0 END) AS sum2006
,SUM(CASE WHEN cmf.financial_year = '2007' THEN 1 ELSE 0 END) AS count2007
,SUM(CASE WHEN cmf.financial_year = '2007' THEN cmf.donation_value ELSE 0 END) AS sum2007
,SUM(CASE WHEN cmf.financial_year = '2008' THEN 1 ELSE 0 END) AS count2008
,SUM(CASE WHEN cmf.financial_year = '2008' THEN cmf.donation_value ELSE 0 END) AS sum2008
,SUM(CASE WHEN cmf.financial_year = '2009' THEN 1 ELSE 0 END) AS count2009
,SUM(CASE WHEN cmf.financial_year = '2009' THEN cmf.donation_value ELSE 0 END) AS sum2009
,SUM(CASE WHEN cmf.financial_year = '2010' THEN 1 ELSE 0 END) AS count2010
,SUM(CASE WHEN cmf.financial_year = '2010' THEN cmf.donation_value ELSE 0 END) AS sum2010
FROM complete_finances cmf
INNER JOIN contact con WITH (nolock) ON con.id = cmf.contact_id
INNER JOIN [l_function] lfu WITH (nolock) ON lfu.code = con.class
LEFT OUTER JOIN event eve with (nolock) on eve.id = cmf.event_id
INNER JOIN [l_approach channel] lac WITH (nolock) ON lac.code = eve.class
WHERE cmf.contact_id IS NOT NULL
GROUP BY
cmf.contact_id
,cmf.type1
,cmf.type2
,lfu.description
,lac.description
WITH CUBE
)
INSERT INTO #category
SELECT
contact_id
,donor_class
,payment_type
,donation_type
,approach_channel
,count1987
,sum1987
,count1988
,sum1988
,count1989
,sum1989
,count1990
,sum1990
,count1991
,sum1991
,count1992
,sum1992
,count1993
,sum1993
,count1994
,sum1994
,count1995
,sum1995
,count1996
,sum1996
,count1997
,sum1997
,count1998
,sum1998
,count1999
,sum1999
,count2000
,sum2000
,count2001
,sum2001
,count2002
,sum2002
,count2003
,sum2003
,count2004
,sum2004
,count2005
,sum2005
,count2006
,sum2006
,count2007
,sum2007
,count2008
,sum2008
,count2009
,sum2009
,count2010
,sum2010
,CASE WHEN count2005 > 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004 = 0
THEN 'New donor'
WHEN count2005 > 0 AND count2004 = 0 AND
count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003 > 0
THEN 'Reactivated donor'
WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
AND count2002 > 0 AND count2001 > 0
THEN '5+yrs consecutive'
WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
AND count2002 > 0
THEN '4yrs consecutive'
WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
THEN '3yrs consecutive'
WHEN count2005 > 0 AND count2004 > 0
THEN '2yrs consecutive'
WHEN count2005 = 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004 > 0
THEN 'Lapsed Donor'
WHEN count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005 = 0
THEN 'Non donor'
END AS category05
,CASE WHEN count2006 > 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005 = 0
THEN 'New donor'
WHEN count2006 > 0 AND count2005 = 0 AND
count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004 > 0
THEN 'Reactivated donor'
WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
AND count2003 > 0 AND count2002 > 0
THEN '5+yrs consecutive'
WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
AND count2003 > 0
THEN '4yrs consecutive'
WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
THEN '3yrs consecutive'
WHEN count2006 > 0 AND count2005 > 0
THEN '2yrs consecutive'
WHEN count2006 = 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005 > 0
THEN 'Lapsed Donor'
WHEN count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006 = 0
THEN 'Non donor'
END AS category06
,CASE WHEN count2007 > 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006 = 0
THEN 'New donor'
WHEN count2007 > 0 AND count2006 = 0 AND
count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005 > 0
THEN 'Reactivated donor'
WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
AND count2004 > 0 AND count2003 > 0
THEN '5+yrs consecutive'
WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
AND count2004 > 0
THEN '4yrs consecutive'
WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
THEN '3yrs consecutive'
WHEN count2007 > 0 AND count2006 > 0
THEN '2yrs consecutive'
WHEN count2007 = 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006 > 0
THEN 'Lapsed Donor'
WHEN count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007 = 0
THEN 'Non donor'
END AS category07
,CASE WHEN count2008 > 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007 = 0
THEN 'New donor'
WHEN count2008 > 0 AND count2007 = 0 AND
count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006 > 0
THEN 'Reactivated donor'
WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
AND count2005 > 0 AND count2004 > 0
THEN '5+yrs consecutive'
WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
AND count2005 > 0
THEN '4yrs consecutive'
WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
THEN '3yrs consecutive'
WHEN count2008 > 0 AND count2007 > 0
THEN '2yrs consecutive'
WHEN count2008 = 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007 > 0
THEN 'Lapsed Donor'
WHEN count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
THEN 'Non donor'
END AS category08
,CASE WHEN count2009 > 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
THEN 'New donor'
WHEN count2009 > 0 AND count2008 = 0 AND
count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007 > 0
THEN 'Reactivated donor'
WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
AND count2006 > 0 AND count2005 > 0
THEN '5+yrs consecutive'
WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
AND count2006 > 0
THEN '4yrs consecutive'
WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
THEN '3yrs consecutive'
WHEN count2009 > 0 AND count2008 > 0
THEN '2yrs consecutive'
WHEN count2009 = 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
THEN 'Lapsed Donor'
WHEN count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
THEN 'Non donor'
END AS category09
,CASE WHEN count2010 > 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
THEN 'New donor'
WHEN count2010 > 0 AND count2009 = 0 AND
count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
THEN 'Reactivated donor'
WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
AND count2007 > 0 AND count2006 > 0
THEN '5+yrs consecutive'
WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
AND count2007 > 0
THEN '4yrs consecutive'
WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
THEN '3yrs consecutive'
WHEN count2010 > 0 AND count2009 > 0
THEN '2yrs consecutive'
WHEN count2010 = 0 AND count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 > 0
THEN 'Lapsed Donor'
WHEN count1987+count1988+count1989+count1990+count1991
+count1992+count1993+count1994+count1995+count1996
+count1997+count1998+count1999+count2000+count2001
+count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009+count2010 = 0
THEN 'Non donor'
END AS category10
,CASE WHEN count1987 > 0 THEN 1987 END AS [1987]
,CASE WHEN count1988 > 0 THEN 1988 END AS [1988]
,CASE WHEN count1989 > 0 THEN 1989 END AS [1989]
,CASE WHEN count1990 > 0 THEN 1990 END AS [1990]
,CASE WHEN count1991 > 0 THEN 1991 END AS [1991]
,CASE WHEN count1992 > 0 THEN 1992 END AS [1992]
,CASE WHEN count1993 > 0 THEN 1993 END AS [1993]
,CASE WHEN count1994 > 0 THEN 1994 END AS [1994]
,CASE WHEN count1995 > 0 THEN 1995 END AS [1995]
,CASE WHEN count1996 > 0 THEN 1996 END AS [1996]
,CASE WHEN count1997 > 0 THEN 1997 END AS [1997]
,CASE WHEN count1998 > 0 THEN 1998 END AS [1998]
,CASE WHEN count1999 > 0 THEN 1999 END AS [1999]
,CASE WHEN count2000 > 0 THEN 2000 END AS [2000]
,CASE WHEN count2001 > 0 THEN 2001 END AS [2001]
,CASE WHEN count2002 > 0 THEN 2002 END AS [2002]
,CASE WHEN count2003 > 0 THEN 2003 END AS [2003]
,CASE WHEN count2004 > 0 THEN 2004 END AS [2004]
,CASE WHEN count2005 > 0 THEN 2005 END AS [2005]
,CASE WHEN count2006 > 0 THEN 2006 END AS [2006]
,CASE WHEN count2007 > 0 THEN 2007 END AS [2007]
,CASE WHEN count2008 > 0 THEN 2008 END AS [2008]
,CASE WHEN count2009 > 0 THEN 2009 END AS [2009]
,CASE WHEN count2010 > 0 THEN 2010 END AS [2010]
FROM category
WHERE
contact_id IS NOT NULL
AND donor_class IS NOT NULL
ORDER BY contact_id