T-SQL | Dynamic name of column with Year and Month

  Kiến thức lập trình

I need to complete the column names based on the Usage.Doc_WarehouseApprovedDate in such a way that the column name is in the format [Year_month]

I had a lot of tries but I can’t figure it out how I can do it simply

DECLARE @DateTo#Q@ nvarchar(max) = '2024-03-31'

SELECT 
<code here>
    CAST(SUM(ISNULL(Usage.[Jan],0)) as INT) as [Jan],   -- Zużycie w styczniu
    CAST(SUM(ISNULL(Usage.[Feb],0)) as INT) as [Feb],   -- Zużycie w lutym
    CAST(SUM(ISNULL(Usage.[Mar],0)) as INT) as [Mar],   -- Zużycie w marcu
    CAST(SUM(ISNULL(Usage.[Apr],0)) as INT) as [Apr],   -- Zużycie w kwietniu
    CAST(SUM(ISNULL(Usage.[May],0)) as INT) as [May],   -- Zużycie w maju
    CAST(SUM(ISNULL(Usage.[Jun],0)) as INT) as [Jun],   -- Zużycie w czerwcu
    CAST(SUM(ISNULL(Usage.[Jul],0)) as INT) as [Jul],   -- Zużycie w lipcu
    CAST(SUM(ISNULL(Usage.[Aug],0)) as INT) as [Aug],   -- Zużycie w sierpniu
    CAST(SUM(ISNULL(Usage.[Sep],0)) as INT) as [Sep],   -- Zużycie we wrześniu
    CAST(SUM(ISNULL(Usage.[Oct],0)) as INT) as [Oct],   -- Zużycie w październiku
    CAST(SUM(ISNULL(Usage.[Nov],0)) as INT) as [Nov],   -- Zużycie w listopadzie
    CAST(SUM(ISNULL(Usage.[Dec],0)) as INT) as [Dec]    -- Zużycie w grudniu
FROM 
    Items   -- Tabela produktów i części
---- <code here>
LEFT JOIN 
    (SELECT 
        Del_ItmID, 
        Warehouse.DIC_IntColumn1,
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 1 THEN Del_Amount ELSE 0 END) AS [Jan],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 2 THEN Del_Amount ELSE 0 END) AS [Feb],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 3 THEN Del_Amount ELSE 0 END) AS [Mar],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 4 THEN Del_Amount ELSE 0 END) AS [Apr],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 5 THEN Del_Amount ELSE 0 END) AS [May],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 6 THEN Del_Amount ELSE 0 END) AS [Jun],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 7 THEN Del_Amount ELSE 0 END) AS [Jul],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 8 THEN Del_Amount ELSE 0 END) AS [Aug],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 9 THEN Del_Amount ELSE 0 END) AS [Sep],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 10 THEN Del_Amount ELSE 0 END) AS [Oct],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 11 THEN Del_Amount ELSE 0 END) AS [Nov],
        SUM(CASE WHEN MONTH(WZ.Doc_WarehouseApprovedDate) = 12 THEN Del_Amount ELSE 0 END) AS [Dec]
---- <code here>
    ) as Usage ON Usage.Del_ItmID = Items.Itm_Id 
        AND Usage.DIC_IntColumn1 = Stock.DIC_IntColumn1 --Pozycje na dokumentach na taki sam produkt

New contributor

user24148853 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

LEAVE A COMMENT