Tame this Beast: TSQL Unpivot
This looks like a good use for UNPIVOT. And you can use common table expressions to avoid repeating yourself. Like this:

WITH DIAGS AS
(
    SELECT DISTINCT VisitID as ID, Diags
    FROM 
    (
        SELECT *
        FROM [AGH00]...[20110128 - AGH00#TXT]
    ) p
    UNPIVOT
    (
        Diags FOR DX IN
        (
            DX01, DX11, DX21, DX31
            , DX02, DX12, DX22, DX32
            , DX03, DX13, DX23, DX33
            , DX04, DX14, DX24, DX34
            , DX05, DX15, DX25, DX35
            , DX06, DX16, DX26, DX36
            , DX07, DX17, DX27, DX37
            , DX08, DX18, DX28, DX38
            , DX09, DX19, DX29, DX39
            , DX10, DX20, DX30, DX40
        )
    ) AS unpvt
),
A0 AS
(
    SELECT VisitID, DischDate, [Type], SubType
    FROM [AGH00]...[20110128 - AGH00#TXT]
),
AC AS
(
    SELECT VisitID, SUM(ChargsAmt) AS Charges
    FROM [AGH00]...[20110128 - AGH00Chg#TXT]
    GROUP BY VisitID
),
AP AS
(
    SELECT VisitID, SUM(Pmt) AS Payments
    FROM [AGH00]...[20110128 - AGH00Pmt#TXT]
    GROUP BY VisitID
)
VISITS AS
(
    SELECT SUBSTRING(A0.DischDate, 7, 4) + SUBSTRING(A0.DischDate, 1, 2) AS YYYYMM
        , A0.[Type]
        , A0.SubType
        , DIAGS.Diags
        , AC.Charges
        , AP.Payments
    FROM DIAGS
        LEFT JOIN A0 ON DIAGS.ID = A0.VisitID
        LEFT JOIN AC ON A0.VisitID = AC.VisitID
        LEFT JOIN AP ON A0.VisitID = AP.VisitID                
)
SELECT YYYYMM, [Type], SubType, Diags
    , COUNT(*) AS [Count]
    , SUM(Charges) AS Charges
    , SUM(Payments) AS Payments
FROM VISITS
GROUP BY YYYYMM, [Type], SubType, Diags
ORDER BY YYYYMM, [Type], SubType, Diags