CODEWITHSUNDEEP
sql-serverdynamicpivot
SouthernGentleman
SouthernGentleman

Reputation: 5

Trouble with SQL Server Dynamic Pivot

I created a Static Pivot just fine but when I did the Dynamic version I kept getting errors like incorrect syntax in the HAVING Clause and other errors like invalid identifier. 

 Select Color,Gender,Size,PreBuyQty,[5277],[5294],[5365]FROM
(SELECT pb.Color, o.OrderID, pb.Gender, pb.SizeNo, pb.Size, pb.PreBuyQty, od.Quantity
FROM         Orders AS o INNER JOIN
                      OrderDetails AS od ON o.OrderID = od.OrderID INNER JOIN
                      ProductsPreBuy AS pb ON od.ProductCode = pb.productcode
GROUP BY pb.Color, pb.Gender, pb.SizeNo, pb.Size, pb.PreBuyQty, od.Quantity, o.OrderID
HAVING     (pb.Color LIKE N'Black') AND (pb.Gender LIKE N'Men')
) As T
PIVOT
(
 SUM(Quantity) FOR OrderID IN ([5277],[5294],[5365])
) As P;

The following is what I've done so far to retrieve it dynamically.

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(o.OrderID)
            FROM         Orders AS o INNER JOIN
                      OrderDetails AS od ON o.OrderID = od.OrderID INNER JOIN
                      ProductsPreBuy AS pb ON od.ProductCode = pb.productcode
         GROUP BY pb.Color, pb.Gender, pb.Size, pb.PreBuyQty, od.Quantity, o.OrderID
         HAVING     (pb.Color LIKE 'Black') AND (pb.Gender LIKE 'Men')
         FOR XML PATH(''), TYPE
         ).value('.', 'NVARCHAR(MAX)')
         ,1,1,'')

Set @query = 'SELECT pb.Color, pb.Gender, pb.Size, pb.PreBuyQty, od.Quantity,' + @cols + ' FROM
(
SELECT pb.Color, o.OrderID, pb.Gender, pb.Size, pb.PreBuyQty, od.Quantity
        FROM Orders AS o INNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID INNER JOIN ProductsPreBuy AS pb ON od.ProductCode = pb.productcode
GROUP BY pb.Color, pb.Gender, pb.Size, pb.PreBuyQty, od.Quantity, o.OrderID
HAVING     (pb.Color LIKE 'Black') AND (pb.Gender LIKE 'Men')
) x 
pivot
(
    Sum(Quantity)
    for OrderID in (' + @cols + ')
    ) p '
execute @query

Upvotes: 0

Views: 18

Answers (1)

ughai
ughai

Reputation: 9890

You can try something like this.

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(o.OrderID)
            FROM         Orders AS o INNER JOIN
                      OrderDetails AS od ON o.OrderID = od.OrderID
                      INNER JOIN ProductsPreBuy AS pb ON od.ProductCode = pb.productcode
         WHERE (pb.Color = 'Black') AND (pb.Gender = 'Men')
         GROUP BY pb.Color, pb.Gender, pb.Size, pb.PreBuyQty, od.Quantity, o.OrderID
         FOR XML PATH(''), TYPE
         ).value('.', 'NVARCHAR(MAX)')
         ,1,1,'')

Set @query = 'SELECT Color, Gender, Size, PreBuyQty,' + @cols + ' FROM
(
SELECT pb.Color, o.OrderID, pb.Gender, pb.Size, pb.PreBuyQty, od.Quantity
        FROM Orders AS o
        INNER JOIN OrderDetails AS od 
            ON o.OrderID = od.OrderID 
        INNER JOIN ProductsPreBuy AS pb 
            ON od.ProductCode = pb.productcode
WHERE (pb.Color = ''Black'') AND (pb.Gender = ''Men'')
GROUP BY pb.Color, pb.Gender, pb.Size, pb.PreBuyQty, od.Quantity, o.OrderID
) x 
PIVOT
(
    Sum(Quantity)
    for OrderID in (' + @cols + ')
    ) p '
execute @query

In your query you don't need to filter using the data using HAVING since your filter do not have an aggregate function. you can use a simple WHERE Clause. Also there are no wildcards in your filter. its better and cleaner to use = in such cases.

Upvotes: 1

Related Questions