SQL variable with Pivot table variable gives syntax error

Question

Hi, my colleague came to me with this error, and now I am connected and trying to figure it out, I hope that some of the experts can help us! Thanks! When I execute Step6, we get this error:

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@cols'. 

---

 --Sample of pivot query --Creating Test Table Step1 CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT) GO -- Inserting Data into Table Step2 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',2) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','SODA',6) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','MILK',1) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','BEER',12) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','MILK',3) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','BEER',24) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',3) GO -- Selecting and checking entires in table Step3 SELECT * FROM Product GO -- Pivot Table ordered by PRODUCT Step4 select * FROM ( SELECT * FROM Product) up PIVOT (SUM(QTY) FOR CUST IN ([FRED], [KATE])) AS pvt ORDER BY PRODUCT GO --dynamic pivot???? Step5 DECLARE @cols NVARCHAR(2000) select @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + b.Cust FROM (select top 100 Cust from tblProduct)b ORDER BY '],[' + b.Cust FOR XML PATH('') ), 1, 2, '') + ']' --Show Step6 SELECT * FROM (SELECT * FROM tblProduct) p PIVOT (SUM(QTY) FOR CUST IN (@cols)) as pvt Order by Product 

Answer 1

For this you need dynamic SQL.

 declare @dyn nvarchar(max) set @dyn = 'SELECT * FROM (SELECT * FROM Product) p PIVOT (SUM(QTY) FOR CUST IN (' + @cols +')) as pvt Order by Product ' exec sp_executesql @dyn 

Answer 2

@ Martin Smith will be right ... when you go to the next error. :-)

As for the error next to @cols, I think you can just wrap it all in brackets. For instance:.

 select @cols = ( STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + b.Cust FROM (select top 100 Cust from tblProduct)b ORDER BY '],[' + b.Cust FOR XML PATH('') ), 1, 2, '') + ']' )