您当前的查询是对产品名称进行透视,并对日期进行分组。但是您需要确定日期并将产品名称分组
试试这个
DECLARE @cols AS NVARCHAR(max),
@query AS NVARCHAR(max)
查找日期的唯一列表,而不是产品名称
SELECT @cols = Stuff((SELECT ','
+ Quotename(CONVERT(char(11), Service_Date, 106))
FROM dbo.Store
WHERE CatID = '2'
GROUP BY CONVERT(char(11), Service_Date, 106)
ORDER BY CONVERT(date, Service_Date)
FOR xml PATH (''), TYPE) .value('.', 'NVARCHAR(MAX)'), 1, 1, '')
在数据透视for
表列表中,使用Service_date
列而不是Product_Name
SET @query = 'SELECT Product_Name,'
+ @cols
+ ' from ( select Service.Service_Date, Store.Product_Name, Servicelist.ProductQty FROM dbo.Service INNER JOIN dbo.Servicelist ON dbo.Service.Service_ID = dbo.Servicelist.Service_ID INNER JOIN dbo.Store ON dbo.Servicelist.Pro_ID = dbo.Store.Pro_ID) x pivot ( SUM(ProductQty) for CONVERT(char(11), Service_Date, 106) in ('
+ @cols + ') ) p '
EXECUTE (@query);