Tame this Beast: TSQL Unpivot

Okay... here's the beast:

```
SELECT
SUBSTRING(DischDate, 7, 4) + SUBSTRING(DischDate, 1, 2) as YYYYMM
,Type
,SubType
,Diags
,Count(*) as Count
,SUM(Charges) as Charges
,SUM(Payments) as Payments
FROM
(
SELECT DISTINCT
ID
,Diags
FROM
(SELECT VisitID as ID
,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
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
) as DIAGS
LEFT JOIN [AGH00]...[20110128 - AGH00#TXT] as A0 on DIAGS.ID = A0.VisitID
LEFT JOIN ( SELECT VisitID, Sum(ChargsAmt) as Charges
FROM [AGH00]...[20110128 - AGH00Chg#TXT]
Group By VisitID ) as AC on A0.VisitID = AC.VisitID
LEFT JOIN ( SELECT VisitID, Sum(Pmt) as Payments
FROM [AGH00]...[20110128 - AGH00Pmt#TXT]
Group By VisitID ) as AP on A0.VisitID = AP.VisitID
Group By
SUBSTRING(DischDate, 7, 4) + SUBSTRING(DischDate, 1, 2)
,Type
,SubType
,Diags
Order By
SUBSTRING(DischDate, 7, 4) + SU

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

StackExchange Code Review Q#490, answer score: 4