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)

友情链接