下面是我創建并在其中插入值的表：
CREATE TABLE prod_sales
(saleID int identity(1,1),
saleDate date,
department varchar(125),
amount int )
GO
INSERT INTO prod_sales VALUES
('2016-01-01','Men''s Clothing & Accessories' ,ROUND(RAND() * 1000, 0)),
('2016-01-02','Phones & Telecommunications',ROUND(RAND() * 1000, 0)),
('2016-01-01','Computer & Office',ROUND(RAND() * 1000, 0)),
('2016-01-02','Consumer Electronics',ROUND(RAND() * 1000, 0)),
('2016-01-01','Men''s Clothing & Accessories' ,ROUND(RAND() * 1000, 0)),
('2016-01-02','Phones & Telecommunications',ROUND(RAND() * 1000, 0)),
('2016-01-01','Computer & Office',ROUND(RAND() * 1000, 0)),
('2016-01-01','Consumer Electronics',ROUND(RAND() * 1000, 0)),
('2016-01-01','Men''s Clothing Accessories' ,ROUND(RAND() * 1000, 0)),
('2016-01-02','Phones & Telecommunications',ROUND(RAND() * 1000, 0)),
('2016-01-02','Computer & Office',ROUND(RAND() * 1000, 0)),
('2016-01-01','Consumer Electronics',ROUND(RAND() * 1000, 0))
GO
SELECT * FROM prod_sales
我的目標是使用Dynamic PIVOT顯示各個部門每天的銷售額總和，查詢結果應如下面的螢屏截圖所示：
為了實作我的目標，我為動態 PIVOT 創建了一個存盤程序，如下所示：
CREATE OR ALTER PROCEDURE DynamicPivot
@ColumnToPivot VARCHAR(50),
@ListToPivot VARCHAR(50)
AS
BEGIN
DECLARE @SqlStatement VARCHAR(MAX)
SET @SqlStatement =
'SELECT saleDate,[Computer & Office],[Consumer Electronics],[Men''s Clothing & Accessories],[Phones & Telecommunications]
FROM
(
SELECT saleDate,department,amount
FROM prod_sales
) AS DataSource
PIVOT
(
SUM(amount)
FOR [' @ColumnToPivot '] IN (' @ListToPivot ')
) AS Pivoting'
EXECUTE (@SqlStatement)
END
EXECUTE DynamicPivot 'department','[Computer & Office],[Consumer Electronics],[Men''s Clothing & Accessories],[Phones & Telecommunications]'
存盤程序已成功創建，但在執行存盤程序后顯示錯誤訊息，如下所示：
如何解決此問題？
uj5u.com熱心網友回復：
問題與單引號無關，而是當您顯然需要更多字符時，您已將字串設定為 50 個字符：
@ListToPivot VARCHAR(50)
嘗試：
@ListToPivot varchar(4000)
另外，為什么存盤程序對您作為引數傳入的列串列進行硬編碼？這也解決了這個問題：
- 示例db<>fiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451037.html
上一篇：根據付款次數生成帳單余額