Dynamic Pivot(在SQL Server 2005中) [英] Dynamic Pivot (in SQL Server 2005)

问题描述

我正在为Microsoft SQL 2005编写存储过程，并且想创建一个动态SQL Pivot:

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

并且不对过程中的所有位置([Location1]，[Location2]等)进行硬编码，但是SQL不接受.

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

我该如何解决?

推荐答案

您不能在纯SQL中做到这一点，必须使用动态SQL并构建要执行的实际SQL.

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

您可以这样做:

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

DECLARE cursor...

LOOP cursor...

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

// end loop

EXEC(@sql)

这篇关于Dynamic Pivot(在SQL Server 2005中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！