T Sql Pivot Table Selecting For IN

I have below query for pivot table only for products 0 , 3 , 11 SELECT * FROM ( SELECT year(createdDate) as [year],month(createdDate) as [month],cp.product_Id as produ

Solution 1:

You have to use DYNAMIC PIVOT something like this:

DECLARE @cols   AS NVARCHAR(MAX) = '',
        @sql    AS NVARCHAR(MAX)

SELECT @cols += N'' + QUOTENAME(ProductID) + ', '
FROM   (
        SELECT DISTINCT ProductID
        FROM Product                         
        ) a                         
SET @cols   = LEFT(@cols, LEN(@cols) - 1)
SET @sql    = N'SELECT * FROM 
                (
                    SELECT 
                        year(createdDate) as [year],month(createdDate) as [month],cp.product_Id as product_ID, 
                        cp.salesprice as Amount 
                    FROM customer_products cp               
                ) x 
                PIVOT
                (
                    SUM(Amount)
                    FOR [product_Id] IN (' + @cols + ') 
                ) p

EXEC Sp_executesql @sql