T sql pivot table selecting for IN

Question

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 product_ID, 
        cp.salesprice as Amount 
    FROM customer_products cp

) as s
PIVOT
(
    SUM(Amount)
FOR [product_Id] IN ([0],[3],[11]) -- for 0 , 3 , 11 products
)AS a

I have also Product table as below

ProductID int

ProductName varchar(50)

Question:

How can i use ( I want to select all ProductID from Product table)

select ProductID from Product

as below

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

    ) as s
    PIVOT
    (
        SUM(Amount)
FOR [product_Id] IN (select ProductID from Product) -- How can ı select all product ID here as select * from Product ?
    )AS a

http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query may be this will help you, we can create dynamic query with pivot — KuldipMCA, Sep 02 '15 at 07:00
thanks for answer i researched about dynamic query however it must not be impossible do like select ID from Table . I am only researching about this. — John Richard, Sep 02 '15 at 07:01
Yes you can use COALESCE function to get all the product id in one variable then use that variable in your dynamic query. — KuldipMCA, Sep 02 '15 at 07:06
You can only do this with dynamic SQL - any particular query in SQL will always produce a result set with a set "shape" - the number of columns, their *names* and types. — Damien_The_Unbeliever, Sep 02 '15 at 07:09

score 0 · Accepted Answer · answered Sep 02 '15 at 07:14

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

T sql pivot table selecting for IN

1 Answers1

Linked