sql - SQL Server 中的按月数据透视（月份将动态变化）

Question

我正在尝试在我的销售数据上创建一个数据透视表，以显示如下所示的每月数据。

我添加了表格代码和数据列表

CREATE TABLE [dbo].[ProductList]
(
    [Product] [nvarchar](255) NULL,
    [ProductName] [nvarchar](255) NULL,
    [SaleDate] [datetime] NULL,
    [Price] [float] NULL,
    [Unit] [nvarchar](255) NULL
) ON [PRIMARY]
GO

这是数据：

INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Beauty Cosmetics', N'Face Pack', CAST(N'2021-02-06T00:00:00.000' AS DateTime), 200, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Beauty Cosmetics', N'Facial Kit', CAST(N'2021-02-07T00:00:00.000' AS DateTime), 43, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Beauty Cosmetics', N'Face Pack', CAST(N'2020-03-04T00:00:00.000' AS DateTime), 200, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Beauty Cosmetics', N'Facial Kit', CAST(N'2020-03-09T00:00:00.000' AS DateTime), 43, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Body Care Products', N'Body Lotion', CAST(N'2020-01-02T00:00:00.000' AS DateTime), 100, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Body Care Products', N'Hair Removal Cream', CAST(N'2020-01-03T00:00:00.000' AS DateTime), 323, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Body Care Products', N'Sunscreen Lotion', CAST(N'2020-01-04T00:00:00.000' AS DateTime), 321, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Body Care Products', N'Petroleum Jelly', CAST(N'2020-09-09T00:00:00.000' AS DateTime), 54, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Body Care Products', N'Hair Removal Cream', CAST(N'2021-02-08T00:00:00.000' AS DateTime), 32, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Body Care Products', N'Sunscreen Lotion', CAST(N'2021-02-09T00:00:00.000' AS DateTime), 211, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Face Cream', N'Fairness Cream', CAST(N'2021-02-03T00:00:00.000' AS DateTime), 2, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Face Cream', N'Men Fairness Cream', CAST(N'2021-02-04T00:00:00.000' AS DateTime), 32, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Face Cream', N'Facial Cream', CAST(N'2021-02-05T00:00:00.000' AS DateTime), 2, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Face Cream', N'Fairness Cream', CAST(N'2021-02-13T00:00:00.000' AS DateTime), 12, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Face Cream', N'Men Fairness Cream', CAST(N'2021-02-14T00:00:00.000' AS DateTime), 23, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Lip Cosmetics', N'Lipstick', CAST(N'2020-03-04T00:00:00.000' AS DateTime), 12, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Lip Cosmetics', N'Matte Lipstick', CAST(N'2021-02-01T00:00:00.000' AS DateTime), 212, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Lip Cosmetics', N'Lip Guard', CAST(N'2021-02-02T00:00:00.000' AS DateTime), 212, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Lip Cosmetics', N'Lip Gloss', CAST(N'2021-02-10T00:00:00.000' AS DateTime), 12, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Lip Cosmetics', N'Matte Lipstick', CAST(N'2021-02-11T00:00:00.000' AS DateTime), 12, N'PC')
INSERT [dbo].[ProductList] ([Product], [ProductName], [SaleDate], [Price], [Unit]) VALUES (N'Lip Cosmetics', N'Lip Guard', CAST(N'2021-02-12T00:00:00.000' AS DateTime), 121, N'PC')

我想喜欢这个

ProductCatagory ProductName     Sale2020    Feruary March   April   May
Beauty Cosmetics    Face Pack   200      0       0       0       0
Beauty Cosmetics    Face Pack   200      0       0       0       0
Beauty Cosmetics    Face Pack   0        200     0       0       0
Beauty Cosmetics    Facial Kit  0        43      0       0       0
Beauty Cosmetics    Facial Kit  43       0       0       0       0

我希望这随着月份与当前月份和总数的变化而动态变化。

标签： sqlsql-serverpivot