Pivot SQL将行转换为列

小编典典

Pivot SQL将行转换为列

sql

下面的查询得到以下结果。

Service Date  Product1 Product2 
01/Jun/2015    1           3
02/Jun/2015    2           5

与其说我希望日期在列中，不如说是这样。

Products  01/Jun/2015  02/Jun/2015 
Product1    1           3
Product2    2           5

询问

DECLARE @cols AS nvarchar(max),
        @query AS nvarchar(max)
SELECT
  @cols = STUFF((SELECT
    ',' + QUOTENAME(Product_Name)
  FROM dbo.Store where CatID='2'

  GROUP BY Product_Name
  ORDER BY Product_Name
  FOR xml PATH (''), TYPE)
  .value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = 'SELECT Replace(CONVERT(NVARCHAR, Service_Date, 106), '' '', ''/'') AS [Service Date],' + @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 Product_Name in (' + @cols + ') ) p '
EXECUTE (@query);

阅读 248

2021-04-15

共1个答案

小编典典

您当前的查询是对产品名称进行透视，并对日期进行分组。但是您需要确定日期并将产品名称分组

试试这个

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);

2021-04-15