我有一个需求,需要按月份显示产品的总销售额,并通过下面的数据透视表完成了这个要求:
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