It appears that unpivot sets the sort order according to the order given in the IN clause, which is what you want anyway.

If you don't trust that, or something more complicated is going on, the simple solution is to use a CASE statement on activities in your order by clause.

example (you can validate it by mixing up the order in your in clause with and without the extra sort).

WITH myCTE as (

SELECT 2013 AS BoundOrLostYear,

CAST(29544.48 AS MONEY) AS TotalNew,

CAST(-10832.00 AS MONEY) AS TotalLost,

CAST(18712.48 AS MONEY) AS Netgain

UNION

SELECT 2012,

35549.67,

-20252.65,

15297.02

UNION

SELECT 2011,

136816.89,

-22860.54,

113956.35

UNION

SELECT 2010,

45795.48,

-54933.17,

-9137.69

UNION

SELECT 2009,

61113,

-19419.3,

41694.5)

SELECT ActivityYear,Activities, Activity

FROM

(

SELECT BoundOrLostYear AS ActivityYear ,

Totalnew ,

TotalLost ,

NetGain

FROM myCTE

) p

UNPIVOT(Activity FOR Activities IN (Totalnew, TotalLost,NetGain )) b

order by ActivityYear desc, CASE Activities WHEN 'Totalnew' Then 1 WHEN 'TotalLost' Then 2 WHEN 'NetGain' THen 3 END