更新时间:2023-02-05 23:20:49
您必须使用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