sql server 2005 - SQL Pivot Table isn't working -

sql 2005

i have temp table:

 year percentmale percentfemale percenthmlss percentemployed totalsrvd 2008 100 0 0 100 1 2009 55 40 0 80 20 2010 64 35 0 67 162 2011 69 27 0 34 285 2012 56 43 10 1 58 

and want create query display data this:

 2008 2009 2010 2011 2012 percentmale 100 55 64 69 56 percentfemale - 40 35 27 43 percenthmlss - - - - 10 percentemployed 100 80 67 34 1 totalsrvd 1 20 162 285 58 

can use pivot table accomplish this? if so, how? i've tried using pivot have found no success.

 select percenthmlss,percentmale,percentfemale, percentemployed,[2008],[2009],[2010],[2011],[2012] (select percenthmlss,percentmale, percentfemale, percentemployed, totalsrvd,year @temptable)as t pivot (sum (totalsrvd) year in ([2008],[2009],[2010],[2011],[2012])) pvt 

this result:

 percenthmlss percentmale percentfemale percentemployed [2008] [2009] [2010] [2011] [2012] 0 55 40 80 null 20 null null null 0 64 35 67 null null 162 null null 0 69 27 34 null null null 285 null 0 100 0 100 1 null null null null 10 56 43 1 null null null null 58 

thanks.

for work want perform unpivot , pivot

select * ( select year, quantity, type ( select year, percentmale, percentfemale, percenthmlss, percentemployed, totalsrvd t ) x unpivot ( quantity type in ([percentmale] , [percentfemale] , [percenthmlss] , [percentemployed] , [totalsrvd]) ) u ) x1 pivot ( sum(quantity) year in ([2008], [2009], [2010], [2011], [2012]) ) p 

see sql fiddle demo

edit further explanation:

you close pivot query tried, in got data year in column format wanted. however, since want data contained in columns percentmale, percentfemale, etc in row of data - need unpivot data first.

basically, doing taking original data , placing in rows based on year. unpivot going place data in format (demo):

year quantity type 2008 100 percentmale 2008 0 percentfemale etc 

once have transformed data format, can perform pivot result want.