user1377504 user1377504 - 1 month ago 9
SQL Question

The column '638' was specified multiple times for 'PVT'. Pivot

I've the following SQL Query

Select Product_Id, [riy] AS [riy],
[eas] AS [eas]
FROM
(SELECT Product_Id, Store_Name, Quantity
FROM [Product_Stock] INNER JOIN Store on Store.Id = [Product_Stock].Stock_Id where Product_Id = 435) ps
PIVOT
(
SUM(Quantity)
FOR Store_Name IN
([riy],[EAST WAREHOUSE - eas])
) AS pvt


it gives the expected result.Giving me total quantity for locations riy and eas.

However, I want to dynamically get the Store names instead of manually specifying them.

this is what I've done.

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME([Product_Id])
FROM [Product_Stock]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SELECT @query =
'SELECT Product_Id FROM
(SELECT Product_Id, Store_Name, Quantity
FROM [Product_Stock] INNER JOIN Store on Store.Id = [Product_Stock].Stock_Id where Product_Id = 435) PS
PIVOT
(
SUM(Quantity)
FOR Store_Name in (' + @cols + ')
) AS PVT'

EXEC SP_EXECUTESQL @query


This gives me an error saying The column '638' was specified multiple times for 'PVT'.

How can i solve this problem?

Answer

Without knowing your actual data this is a blind flight, but I think you have two issues:

  • You must use DISTINCT to get each value only once
  • You are not concatenating the stores names but the IDs of your products

Try this

SELECT @cols = STUFF((SELECT DISTINCT  ',' + QUOTENAME([Store_Name]) 
                FROM [Product_Stock]
                INNER JOIN Store on Store.Id = [Product_Stock].Stock_Id
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
Comments