T sql数据透视表为IN选择 [英] T sql pivot table selecting for IN

问题描述

我在下面仅查询产品0，3，11的数据透视表

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

我还有如下的产品表

ProductID int

ProductName varchar(50)

问题:

我该如何使用(我想从产品"表中选择所有产品ID)

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

select ProductID from Product

如下

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

推荐答案

您必须使用DYNAMIC PIVOT这样的东西:

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数据透视表为IN选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！