Error with Dynamic Pivot Query

Question

I want to Create a query which pivoting my data to get every Order with the total of quantity of its items,but i have a lot of items in my data and that will be Hard if i Write every item in the select Statement And every item in the PIVOT function so that I tried to apply Dynamic query on my query but i have many error so can any one help me to know whats the wrong with it? this is my query.

--VARIABLE TO HOLD Items--
DECLARE @ItemName NVARCHAR(500)
--VARIABLE TO HOLD CODE--
DECLARE @SQL NVARCHAR(MAX)
--TEMP TABLE --
CREATE  #temp (COLUMNVALS NVARCHAR(500))
--INSERT DISTINCT DATES INTO TEMP TABLE--
INSERT INTO #temp
SELECT DISTINCT Item FROM
(SELECT 
      OtherLangDescription  AS Item
FROM  Warehouse.WHWorkOrderHeader 
INNER JOIN
      Warehouse.WHWorkOrderDetails ON Warehouse.WHWorkOrderHeader.ID = Warehouse.WHWorkOrderDetails.WHWorkOrderHeaderId 
INNER JOIN
      Warehouse.StockItems ON Warehouse.WHWorkOrderDetails.StockItemId = Warehouse.StockItems.Id 
)
--CONCAT INTO SELECT LIST--
SET @ItemName = COALESCE(@ItemName+', ','') + '[' + Item + ']' FROM #temp
--CREATE THE SELECT STATEMENT--
SELECT @SQL = '
WITH t_PVT AS (
SELECT
      Warehouse.WHWorkOrderHeader.ID
    , OtherLangDescription
    , Type
    , Warehouse.WHWorkOrderDetails.Quantity
    , Warehouse.WHWorkOrderDetails.Value
    , Value*Quantity AS Total

FROM   Warehouse.WHWorkOrderDetails 
   INNER JOIN Warehouse.WHWorkOrderHeader 
            ON Warehouse.WHWorkOrderDetails.WHWorkOrderHeaderId = Warehouse.WHWorkOrderHeader.ID 
   INNER JOIN Warehouse.Warehouse 
            ON Warehouse.WHWorkOrderHeader.WarehouseId = Warehouse.Warehouse.Id 
   INNER JOIN Warehouse.StockItems 
            ON Warehouse.WHWorkOrderDetails.StockItemId = Warehouse.StockItems.Id 
   INNER JOIN Sales.sls_Customers 
            ON Warehouse.WHWorkOrderHeader.CustomerId = Sales.sls_Customers.ID 
)
SELECT 
       Warehouse.WHWorkOrderHeader.ID
     , ' + Item + '
     FROM t_PVT
PIVOT
(
 SUM(Warehouse.WHWorkOrderDetails.Quantity) 
 FOR Item IN (' + Item + ') AS PVT'

--PRINT IT TO SEE WHAT IT'S DONE--
PRINT @SQL
--EXECUTE IT--
EXEC (@SQL)

And this is the errors which appears:

score 0 · Accepted Answer · answered Nov 02 '17 at 15:05

0

Your temp table declaration is missing the table keyword.

CREATE TABLE #temp (COLUMNVALS NVARCHAR(500))

But that isn't the end of your issues here. You reference item in the dynamic query but there is no table mentioned. And if you mean to use the temp table you have another issue because you are selecting a scalar value.

answered Nov 02 '17 at 15:05

Sean Lange

33,028
3
25
40

Still have this error (Msg 156, Level 15, State 1, Line 19 Incorrect syntax near the keyword 'SET'. Msg 156, Level 15, State 1, Line 19 Incorrect syntax near the keyword 'FROM'.) – Mümin Nov 02 '17 at 15:08
Did you read my whole answer? You have a LOT of problems with your code. – Sean Lange Nov 02 '17 at 15:08
Thanks for your help, but i still need more :D Because its first time for me to create the Dynamic pivot, so can you clarify how to use the temp table and select a scalar value or another ? – Mümin Nov 02 '17 at 15:19
Look at your query. You don't have a column name Item in your temp table. It is called COLUMNVALS. To be honest not really sure why you need that temp table but whatever. Then look at your dynamic query, there is no from. But you probably want to be using your @ItemName variable here. It really comes down to paying attention to the details. – Sean Lange Nov 02 '17 at 15:21
thank you for your time,i'm really thankful For your help. – Mümin Nov 02 '17 at 15:29

Error with Dynamic Pivot Query

1 Answers1