動態填充月份為Month1、month2等而不是月份名稱

我正在為過去 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：添加 了實際結果和期望結果的圖片結果和預期結果影像

uj5u.com熱心網友回復：

所以有一個很棒的內置函式呼叫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

標籤：sql sql服务器 tsql 动态的

上一篇：如何獲取關于SQL中員工代碼的休假數？

下一篇：如何使用TSQLforSQLServer從文本欄位中提取日期