How to change query format

[CoolFactor]

I have the following query:
ID Competency StartDate EndDate FTE
1 PM 10/1/2009 9/30/2010 1
2 PM 10/1/2009 9/30/2010 1
3 PM 10/1/2009 9/30/2010 1
1 PM 10/1/2010 9/30/2011 1
2 PM 10/1/2010 9/30/2011 1
3 PM 10/1/2010 9/30/2011 1
1 PM 10/1/2011 9/30/2012 1
2 PM 10/1/2011 9/30/2012 1
3 PM 10/1/2011 9/30/2012 1
1 PM 10/1/2012 9/30/2013 1
2 PM 10/1/2012 9/30/2013 1
3 PM 10/1/2012 9/30/2013 1
1 PM 10/1/2013 9/30/2014 1
2 PM 10/1/2013 9/30/2014 1
3 PM 10/1/2013 9/30/2014 1
1 PM 10/1/2014 9/30/2015 1
2 PM 10/1/2014 9/30/2015 1
3 PM 10/1/2014 9/30/2015 1

How can I make this query look like this:

Competency 2010 2011 2012 2013 2014 2015
PM 3 3 3 3 3 3

 

[markros]

Depending on your SQL Server version you may look into the new PIVOT command in SQL Server 2005 and up or use CASE statements for your sum and hardcoding values.

 

[markros]

See also

http://forums.asp.net/p/1408960/3087689.aspx#3087689