Dynamic Pivot (in SQL Server 2005)

Question

I'm writing a stored procedure for Microsoft SQL 2005 and I want to create a dynamic SQL Pivot:

SELECT Book.ISBN,
       Book.Name
       StockMutation.StockLocation
FROM   Book INNER JOIN StockMutation AS sm ON Book.bookid = sm.bookid
PIVOT
(
       COUNT(sm.NumberOfBooks)
       FOR sm.StockLocation IN (...)
)

Preferable I want to replace (...) with:

SELECT StockLocation.StockLocation FROM StockLocation

and not hardcode all locations in the procedure ([Location1],[Location2],etc.), but SQL doesn't accept this.

How do I solve this?

Answer 1

You can't do it in pure SQL, you have to use dynamic SQL and build the actual SQL you want to execute.

You can do this like so:

DECLARE @sql VARCHAR(8000)
SET @sql = 'FOR sm.StockLocation IN ('

DECLARE cursor...

LOOP cursor...

SET @sql = @sql + '''' + column_name + ''','

// end loop

EXEC(@sql)