Dynamically Populating months as Month1, month2 etc instead of month name-CodePudding

I am creating a report for the number of enquiries received in the last 13 months which I get in a tabular format, I have managed to pivot the data and dynamically populate the months. However, I need the columns to be as Month1, Month2, Month3 etc rather than Jan-2022, Feb-22 etc.

Any help on how to do this would be greatly appreciated.

--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

edit: code for 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: added pic result and expected result image of actual result and desired result

CodePudding user response：

So there is a great built in function call month(), which returns the month number (and in this scenario of the current date). To try it out run this in a query editor to demonstrate. Then all you need to do is to incorporate it into youre code

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