Dynamic SQL Pivot

DYNAMIC SQL PIVOT

One problem with the PIVOT query is that you have to specify all values inside the IN selection if you want to see them as columns.

A quick way to circumvent this problem is to create a dynamic IN selection making your PIVOT dynamic.

DECLARE @query VARCHAR(4000)

DECLARE @languages VARCHAR(2000)

SELECT @languages =

        STUFF((SELECT DISTINCT '],['+LTRIM([Language])FROM [dbo].[BookList]

        ORDER BY '],['+LTRIM([Language]) FOR XML PATH('') ),1,2,'') + ']'

SET @query=

'SELECT * FROM

  (SELECT YearBoughtIntoStore AS [Year Bought],[Language],NumberOfBooks

   FROM BookList) sourceData

PIVOT(SUM(NumberOfBooks)FOR [Language] IN ('+ @languages +')) pivotrReport' EXECUTE(@query)