Dynamic Pivot (in SQL Server 2005)
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 n开发者_开发问答ot hardcode all locations in the procedure ([Location1],[Location2],etc.), but SQL doesn't accept this.
How do I solve this?
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)
精彩评论