动态填充月份为 Month1、month2 等，而不是月份名称

Question

我正在创建一份关于过去 13 个月内收到的查询数量的报告，该报告以表格格式获得，我已成功地旋转数据并动态填充月份。但是，我需要将列设置为 Month1、Month2、Month3 等，而不是 Jan-2022、Feb-22 等。

任何有关如何执行此操作的帮助将不胜感激。

--Sample data
DROP TABLE Admission
create table Admission (AdmissionStatus VARCHAR(10), LeadDate DATE, Total INT)
INSERT INTO Admission VALUES ('Enquiry', '2021-03-01', 61)
INSERT INTO Admission VALUES ('Enquiry', '2021-04-01', 29)
INSERT INTO Admission VALUES ('Enquiry', '2021-05-01', 39)
INSERT INTO Admission VALUES ('Enquiry', '2021-06-01', 44)
INSERT INTO Admission VALUES ('Enquiry', '2021-07-01', 33)
INSERT INTO Admission VALUES ('Enquiry', '2021-08-01', 17)
INSERT INTO Admission VALUES ('Enquiry', '2021-09-01', 66)
INSERT INTO Admission VALUES ('Enquiry', '2021-10-01', 58)
INSERT INTO Admission VALUES ('Enquiry', '2021-11-01', 38)
INSERT INTO Admission VALUES ('Enquiry', '2021-12-01', 26)
INSERT INTO Admission VALUES ('Enquiry', '2022-01-01', 40)
INSERT INTO Admission VALUES ('Enquiry', '2022-02-01', 25)
INSERT INTO Admission VALUES ('Enquiry', '2022-03-01', 0)



 

DECLARE
@DynamicPivotQuery AS NVARCHAR(MAX),
@ColumnName AS NVARCHAR(MAX)

--Populate Column names with dates for last 13 months
SELECT @ColumnName = ISNULL(@ColumnName + ', ','') + QUOTENAME(LEFT(DATENAME(month, dt),3) + '-' + DATENAME(YEAR, dt) )
FROM (SELECT dateadd(month, n, DATEADD(month, -12, DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()))) as dt FROM dbo.GetNums(0,12) as dt) AS dt

--Pivot Data with dynamic date column
SET @DynamicPivotQuery = 
N'SELECT * FROM 
(
    SELECT AdmissionStatus, LEFT(DATENAME(month, LeadDate), 3) + ''-'' + DATENAME(YEAR, LeadDate) as LeadDate, Total
    FROM Admission
) d
pivot
(
    SUM(Total)
    for LeadDate IN (' + @ColumnName + ')
) piv'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

编辑：dbo.GetNum 的代码

ALTER FUNCTION [dbo].[GetNums] (@Low as BIGINT, @High as BIGINT) RETURNS TABLE
    AS
    RETURN
    WITH
    L0 as (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) as D(c)),
    L1 as (SELECT 1 AS  c FROM L0 AS A CROSS JOIN L0 as B),
    L2 as (SELECT 1 AS  c FROM L1 AS A CROSS JOIN L1 as B),
    L3 as (SELECT 1 AS  c FROM L2 AS A CROSS JOIN L2 as B),
    L4 as (SELECT 1 AS  c FROM L3 AS A CROSS JOIN L3 as B),
    L5 as (SELECT 1 AS  c FROM L4 AS A CROSS JOIN L4 as B),
    Nums As (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high -  @Low + 1) @Low + rownum - 1 as n
    FROM Nums
    ORDER BY rownum;

edit2：添加图片结果和预期结果 实际结果和期望结果的图像

Answer 1

因此，有一个很棒的内置函数调用month()，它返回月份数（在这种情况下返回当前日期）。要尝试一下，请在查询编辑器中运行此命令进行演示。然后您需要做的就是将其合并到您的代码中

DROP TABLE if exists #Admission;

create table #Admission (AdmissionStatus VARCHAR(10), LeadDate DATE, Total INT)

INSERT INTO #Admission VALUES
('Enquiry', '2021-03-01', 61),
('Enquiry', '2021-04-01', 29),
('Enquiry', '2021-05-01', 39),
('Enquiry', '2021-06-01', 44),
('Enquiry', '2021-07-01', 33),
('Enquiry', '2021-08-01', 17),
('Enquiry', '2021-09-01', 66),
('Enquiry', '2021-10-01', 58),
('Enquiry', '2021-11-01', 38),
('Enquiry', '2021-12-01', 26),
('Enquiry', '2022-01-01', 40),
('Enquiry', '2022-02-01', 25),
('Enquiry', '2022-03-01', 0)

DECLARE
    @DynamicPivotQuery AS NVARCHAR(MAX),
    @ColumnName AS NVARCHAR(MAX)

SELECT 
    @ColumnName = STRING_AGG('Month' +CAST(RowNum AS VARCHAR(4)), ', ')
FROM 
    (
        SELECT 
            DATEADD(month, n, DATEADD(month, -12, DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()))) as dt
            , ROW_NUMBER() OVER (ORDER BY n) AS RowNum 
        FROM 
            dbo.GetNums(0,12) as dt
    ) AS dt

SET @DynamicPivotQuery = 
N'SELECT * 
    FROM 
        (
            SELECT AdmissionStatus
            , Total
            , ''Month''+CAST(ROW_NUMBER() OVER (ORDER BY LeadDate ASC) AS VARCHAR(12)) AS MonthValue
            FROM #Admission
        ) d
    pivot
        (
            SUM(Total)
            for MonthValue IN (' + @ColumnName + ')
        ) piv'

EXEC sp_executesql @DynamicPivotQuery