返回带有数据透视表的多列数据

Question

返回带有数据透视表的多列数据

sqlsql-serverdatabasesql-server-2008

3

我有一个需求，需要按月份显示产品的总销售额，并通过下面的数据透视表完成了这个要求：

SELECT *
FROM (SELECT m.ProductId [Product],  
       DATENAME(MONTH, m.OrderDate) [Month], 
       SUM(ISNULL(M.Quantity, 0)) [Sales]
      FROM SampleOrders m
      GROUP BY m.ProductId, 
      DATENAME(MONTH, m.OrderDate)) AS MontlySalesData
PIVOT( SUM([Sales])   
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS PivotData

输出:

Product January February    March   April   May June    July    August  September   October November    December
1001    NULL    NULL    NULL    NULL    6   30  NULL    NULL    NULL    NULL    NULL    NULL
1002    NULL    NULL    NULL    NULL    14  6   NULL    NULL    NULL    NULL    NULL    NULL

所以在五月和六月，“OrderDate”列的数据显示如预期。但我有另一个要求，我需要显示来自另一个表的透视数据以及按月份的库存数据。为了简化问题，我需要以下输出结果:

Product May May-Stock June June-Stock
1001    6   10        30   20
1002    14   6         6   10

我不确定如何实现这一点，我有点困惑是否可以同时使用两个数据透视表来获得结果。如果您有任何想法，将不胜感激。

注意：下面是带有示例数据的表模式。

CREATE TABLE [dbo].[SampleOrders](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NULL,
    [OrderDate] [datetime] NULL,
    [CustomerId] [int] NULL,
    [Quantity] [float] NULL,
 CONSTRAINT [PK_SampleOrders] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Id  ProductId   OrderDate   CustomerId  Quantity
1   1001    2019-06-10 00:00:00.000 1   10
2   1001    2019-06-01 00:00:00.000 1   20
3   1002    2019-06-02 00:00:00.000 2   2
4   1002    2019-06-20 00:00:00.000 2   4
5   1001    2019-05-20 00:00:00.000 1   6
6   1002    2019-05-22 00:00:00.000 1   14

CREATE TABLE [dbo].[SampleStock](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NULL,
    [Quantity] [float] NULL,
    [Status] [int] NULL,
    [StockDate] [datetime] NULL,
 CONSTRAINT [PK_SampleStock] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Id  ProductId   Quantity    Status  StockDate
1   1001    20  1   2019-06-10 00:00:00.000
2   1002    10  1   2019-06-12 00:00:00.000
3   1001    10  1   2019-05-02 00:00:00.000
4   1002    4   2   2019-05-20 00:00:00.000

- AT-2017

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Khairul Alam · Accepted Answer

这可能会有所帮助

CREATE TABLE [dbo].[SampleOrders](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NULL,
    [OrderDate] [datetime] NULL,
    [CustomerId] [int] NULL,
    [Quantity] [float] NULL,
 CONSTRAINT [PK_SampleOrders] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SampleStock](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NULL,
    [Quantity] [float] NULL,
    [Status] [int] NULL,
    [StockDate] [datetime] NULL,
 CONSTRAINT [PK_SampleStock] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO SampleOrders(ProductId,OrderDate,CustomerId,Quantity) VALUES(1001,'2019-06-10 00:00:00.000', 1,   10)
INSERT INTO SampleOrders(ProductId,OrderDate,CustomerId,Quantity) VALUES(1001,'2019-06-01 00:00:00.000', 1,   20)
INSERT INTO SampleOrders(ProductId,OrderDate,CustomerId,Quantity) VALUES(1002,'2019-06-02 00:00:00.000', 2,   2 )
INSERT INTO SampleOrders(ProductId,OrderDate,CustomerId,Quantity) VALUES(1002,'2019-06-20 00:00:00.000', 2,   4 )
INSERT INTO SampleOrders(ProductId,OrderDate,CustomerId,Quantity) VALUES(1001,'2019-05-20 00:00:00.000', 1,   6 )
INSERT INTO SampleOrders(ProductId,OrderDate,CustomerId,Quantity) VALUES(1002,'2019-05-22 00:00:00.000', 1,   14)
GO
INSERT INTO SampleStock(ProductId,Quantity,Status,StockDate) VALUES (1001,20,1,'2019-06-10 00:00:00.000')
INSERT INTO SampleStock(ProductId,Quantity,Status,StockDate) VALUES (1002,10,1,'2019-06-12 00:00:00.000')
INSERT INTO SampleStock(ProductId,Quantity,Status,StockDate) VALUES (1001,10,1,'2019-05-02 00:00:00.000')
INSERT INTO SampleStock(ProductId,Quantity,Status,StockDate) VALUES (1002,6 ,2,'2019-05-20 00:00:00.000')
INSERT INTO SampleStock(ProductId,Quantity,Status,StockDate) VALUES (1003,4 ,2,'2019-05-20 00:00:00.000')
GO
SELECT * FROM SampleOrders
SELECT * FROM SampleStock
GO
-- DROP TABLE #MonthlySales
SELECT Product
      ,January as JanuarySales
      ,February as FebruarySales
      ,March as MarchSales
      ,April as AprilSales
      ,May as MaySales
      ,June as JuneSales
      ,July as JulySales
      ,August as AugustSales
      ,September as SeptemberSales
      ,October as OctoberSales
      ,November as NovemberSales
      ,December as DecemberSales
INTO #MonthlySales
FROM (SELECT m.ProductId [Product],  
       DATENAME(MONTH, m.OrderDate) [Month], 
       SUM(ISNULL(M.Quantity, 0)) [Sales]
      FROM SampleOrders m
      GROUP BY m.ProductId, 
      DATENAME(MONTH, m.OrderDate)) AS MontlySalesData
PIVOT( SUM([Sales])   
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS PivotData

GO
-- DROP TABLE #MonthlyStock
SELECT Product
      ,January as JanuaryStock 
      ,February as FebruaryStock
      ,March as MarchStock
      ,April as AprilStock
      ,May as MayStock
      ,June as JuneStock
      ,July as JulyStock
      ,August as AugustStock
      ,September as SeptemberStock
      ,October as OctoberStock
      ,November as NovemberStock
      ,December as DecemberStock
INTO #MonthlyStock
FROM (SELECT m.ProductId [Product],  
       DATENAME(MONTH, m.StockDate) [Month], 
       SUM(ISNULL(M.Quantity, 0)) [Stock] 
      FROM SampleStock m
      GROUP BY m.ProductId, 
      DATENAME(MONTH, m.StockDate)) AS MontlyStock
PIVOT( SUM([Stock])   
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS PivotData

GO
SELECT ISNULL(sl.Product,st.Product) as Product
      ,JanuarySales,JanuaryStock
      ,FebruarySales,FebruaryStock
      ,MarchSales,MarchStock
      ,AprilSales,AprilStock
      ,MaySales,MayStock
      ,JuneSales,JuneStock
      ,JulySales,JulyStock
      ,AugustSales,AugustStock
      ,SeptemberSales,SeptemberStock
      ,OctoberSales,OctoberStock
      ,NovemberSales,NovemberStock
      ,DecemberSales,DecemberStock
FROM #MonthlySales sl
FULL JOIN #MonthlyStock st ON sl.Product = st.Product