How to solve the following error message "Unclosed quotation mark after the character string&qu-CodePudding

Below is the table I have created and inserted values in it:

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

My aim is to to display the sum of sales, each day, for each of the various departments using Dynamic PIVOT and the query result should be something like which is shown in the screenshot below:

In order to achieve my aim, I have created a Stored Procedure for Dynamic PIVOT which is shown below:

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]'

The stored procedure was successfully created but after executing the stored procedure it is displaying an error message which is shown below:

How can this issue be fixed ?

CodePudding user response：

The problem has nothing to do with the single quote, it's that you've set your string to 50 characters when you clearly need more than that:

@ListToPivot VARCHAR(50)

Try:

@ListToPivot varchar(4000)

Also why does the stored procedure hard-code the list of columns that you're passing in as a parameter? This fixes that too:

Example db<>fiddle