Summary: I’m trying to use a SQL PIVOT to get Gender as a SubCategory of AgeBand. I’ve got AgeBand working (see Below), but not sure how to add the second level in a SQL PIVOT (SQL Server).

I have a SQL Query that uses a PIVOT to get data from a table looking like this:

…and so on…

To look like this:

…

SQL for doing that works fine:

SELECT County, Section, MeasureCode, MeasureDesc
    , MAX(CASE WHEN theValue = 'Members' THEN CAST([Overall] as int) ELSE 0 END) as [Overall Members]
    , MAX(CASE WHEN theValue = 'Percent' THEN CAST([Overall] as decimal(10,2)) ELSE 0 END) as [Overall Percent]
    , MAX(CASE WHEN theValue = 'Members' THEN CAST([65-74] as int) ELSE 0 END) as [65-74 Members]
    , MAX(CASE WHEN theValue = 'Percent' THEN CAST([65-74] as decimal(10,2)) ELSE 0 END) as [65-74 Percent]
    
    , MAX(CASE WHEN theValue = 'Members' THEN CAST([75-84] as int) ELSE 0 END) as [75-84 Members]
    , MAX(CASE WHEN theValue = 'Percent' THEN CAST([75-84] as decimal(10,2)) ELSE 0 END) as [75-84 Percent]
    
    , MAX(CASE WHEN theValue = 'Members' THEN CAST([85+] as int) ELSE 0 END) as [85+ Members]
    , MAX(CASE WHEN theValue = 'Percent' THEN CAST([85+] as decimal(10,2)) ELSE 0 END) as [85+ Percent]
    
FROM (
        SELECT 'Members' as theValue
            , County, Section, MeasureCode, MeasureDesc
            , [Overall], [65-74], [75-84], [85+]
        FROM #cond a
        PIVOT(SUM(MemberCount)
            FOR AgeBand IN ([Overall], [Under 65], [65-74], [75-84], [85+])
            ) b

        UNION
        SELECT 'Percent' as theValue
            , County, Section, MeasureCode, MeasureDesc
            , [Overall], [65-74], [75-84], [85+]
        FROM #cond a
        PIVOT(MAX(PctTotal)
            FOR AgeBand IN ([Overall], [65-74], [75-84], [85+])
            ) c
    ) e
    
GROUP BY County, Section, MeasureCode, MeasureDesc
ORDER BY Section
    , MAX(CASE WHEN Section = '13. Another Section' THEN CAST(MeasureDesc as int)
                        WHEN theValue = 'Percent' THEN CAST([Overall] as decimal(10,2))
                        WHEN theValue = 'Members' THEN CAST([Overall] as decimal(10,2))
                        ELSE 0 
                END) DESC

As you can see though, in the original data set, there is a column for Gender. I want to add that as a SubCategory of the AgeBands so that it will look like this (added color and borders to make it more clear):

However, I’m am completely stuck on how to approach that in the SQL. Can someone point my in the correct direction?