SQL Server 2014存储过程：用Pivot实现单月列下双聚合列展示

Got it, let's tackle your requirement step by step. You need to display a single Month column paired with two aggregated values: TotalAmount (from table AB, linked to A via the ID field) and TotalHours (from table CD, where C is related to A). Here's how you can structure your SQL Server 2014 stored procedure to achieve this:

ALTER PROCEDURE YourProcedureName
    -- Add any input parameters here if needed
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- CTE to aggregate Amount from AB table, linked to A
    WITH AggregatedAmount AS (
        SELECT
            AB.Month,
            SUM(AB.Amount) AS TotalAmount
        FROM
            A
            INNER JOIN AB ON A.ID = AB.ID
        WHERE
            -- Add date filters if using parameters
            (@StartDate IS NULL OR AB.Month >= @StartDate)
            AND (@EndDate IS NULL OR AB.Month <= @EndDate)
        GROUP BY
            AB.Month
    ),
    -- CTE to aggregate Hours from CD table, linked through C to A
    AggregatedHours AS (
        SELECT
            CD.Month, -- Adjust if Month resides in table C instead
            SUM(CD.Hours) AS TotalHours
        FROM
            A
            INNER JOIN C ON A.RelatedEntityID = C.RelatedEntityID -- Replace with your actual join condition
            INNER JOIN CD ON C.CID = CD.CID -- Replace with your actual join condition between C and CD
        WHERE
            -- Match the same date filters if needed
            (@StartDate IS NULL OR CD.Month >= @StartDate)
            AND (@EndDate IS NULL OR CD.Month <= @EndDate)
        GROUP BY
            CD.Month
    )
    -- Combine both aggregates into a single result set with one Month column
    SELECT
        COALESCE(aa.Month, ah.Month) AS Month,
        ISNULL(aa.TotalAmount, 0) AS TotalAmount,
        ISNULL(ah.TotalHours, 0) AS TotalHours
    FROM
        AggregatedAmount aa
        FULL OUTER JOIN AggregatedHours ah ON aa.Month = ah.Month
    ORDER BY
        COALESCE(aa.Month, ah.Month);
END
GO

Key Adjustments for Your Setup: #

• Join Conditions: Swap the placeholder columns (like A.RelatedEntityID = C.RelatedEntityID) with your actual foreign key relationships between tables A, C, and CD.
• Month Column Source: If the Month value for hours data comes from table C instead of CD, update the AggregatedHours CTE to use C.Month.
• Filter Logic: Keep or remove the date parameters based on your stored procedure's input needs.
• Null Handling: ISNULL replaces missing aggregate values with 0—if you prefer to keep NULLs instead, just remove this function.

This structure ensures your Month column is unified, with both metrics aligned correctly even if some months only have data for one of the two values.

内容的提问来源于stack exchange，提问作者Sandeep Kumar