Dynamic Pivots

SSAS and SSRS
1

If I have a table called Rates and this table has two columns (Year and Rate).

EG

Year Rate
2015 2.25
2014 2.75
2013 3.25
2012 2.75
2011 3.50
2010 2.45
2009 4.51
2008 3.27
2007 2.25
2006 4.25

I need to pull the latest 5 years and the corresponding rates into a result set like this. This is causing me problems because the years being pulled will change year on year.

2015 2014 2013 2012 2011
2.25 2.75 3.25 2.75 3.50

Any help gratefully received

2

You can use dynamic pivot as explained in this article. Alternatively, if you can live with generic column headings, you could do the following

;WITH cte AS
(
	SELECT TOP (5) Rate,
		DENSE_RANK() OVER (ORDER BY [year] DESC) AS RN
	FROM
		YourTable
	ORDER BY
		[Year] DESC 
)
SELECT *
FROM
	cte
PIVOT (MAX(Rate) FOR RN IN ([1],[2],[3],[4],[5]))P