Question

Hi my coworker came to me with this error and now I am hooked and trying to figure it out, hope some of the experts can help us! Thanks so much! 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

很遗憾,你们需要充满活力才能做到这一点。

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将正确......当你发现下一个错误时。

关于@cols附近的错误,我认为你只能总结括号中的全部错误。例如:

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,   ) +  ] 
               )

友情链接