Using an UNPIVOT, GROUP BY, and a couple of COUNT(*) functions (one a window function), you can get most of what you need:

SELECT
    *,
    Total * 100 / SUM(Total) OVER(PARTITION BY Attribute) AS Percentage
FROM (
    SELECT Attribute, Category, COUNT(*) AS Total
    FROM DATA D
    UNPIVOT (
        Category
        FOR Attribute IN (sex, state, race)
    ) U
    GROUP BY Attribute, Category
) G
ORDER BY Attribute, Total DESC, Category

But the above does not give you a “Total” row.

Instead, you can use a CROSS APPLY (VALUES...) to perform the equivalent of an UNPIVOT while adding an extra element to give you the “Total” row. This also allows your to more easily define the results order.

SELECT
    *,
    Total * 100 / SUM(Total) OVER(PARTITION BY Attribute) AS Percentage
FROM (
    SELECT Ord, Attribute, Category, COUNT(*) AS Total
    FROM DATA D
    CROSS APPLY (
        VALUES
            (1, 'Total', 'Total'),
            (2, 'sex', sex),
            (3, 'state', state),
            (4, 'race', race)
    ) V(Ord, Attribute, Category)
    GROUP BY Ord, Attribute, Category
) G
ORDER BY Ord, Total DESC, Category

Results:

The above does not map the source data to the specific values you have shown, but that should be a simple matter of adding a few joins to the associated lookup tables and replacing the specific value referenced below as needed. You can of course also omit the result columns that you do not need.

See this db<>fiddle for a demo.

This can be done with grouping sets:

with DataCategories as (
    select
        case sex when 'M' then 'Male' when 'F' then 'Female' else 'Other/Declined' end as SexCategory,
        case state when 'TX' then 'In State' else 'Out State' end as RegionCategory,
        case race when 'A' then 'Asian' when 'B' then 'Black' when 'W' then 'White' when 'H' then 'Hispanic' else race end as RaceCategory,
        count(*) over () as Total
    from Data  
)
select
    coalesce(SexCategory, RegionCategory, RaceCategory, 'Total') as Category,
    count(*) as Total,
    cast(count(*) * 100.0 / min(Total) as decimal(5, 2)) as Percentage,
    /* another way to count the total rows dynamically */
    sum(count(*)) over (partition by grouping_id(SexCategory, RegionCategory, RaceCategory)) as Total2    
from DataCategories
group by grouping sets ( (), (SexCategory), (RegionCategory), (RaceCategory) )
order by coalesce(nullif(grouping_id(SexCategory, RegionCategory, RaceCategory), 7), 1);

https://dbfiddle.uk/QQ2cj_I9

One possible advantage to this approach is that you wouldn’t necessarily need to list out all the values for the unpivot operation. If say a new race code snuck into the data then it could just be left as is/untranslated and the category would still appear. And it’s even easier for values that should display unchanged.

You can use unpivot and grouping.

See example

select data,case when data<>'total' then info else 'Total' end category
  ,count(distinct userid) qty
  ,count(userid)*100.0/max(case when data='total' then cast(info as int) end )over() pct
from(
SELECT userid,data,info FROM ( 
  SELECT userid -- cast(userid as varchar(10)) userid
    ,CAST(case when sex='M' then 'Male' when sex='F' then 'Female' 
          else 'Other/Declined' end 
          AS varchar(10)) sex
    ,CAST(case when state='TX' then 'In State' 
          else 'Out State' end AS varchar(10)) state
    ,CAST(case when race='W' then 'White' when race='B' then 'Black' 
               when race='H' then 'Hispanic'
          else 'Undefined race' end
          AS varchar(10)) race
    ,cast(count(*)over() as varchar(10)) total
  FROM userdata 
  ) x 
UNPIVOT( info 
FOR data IN( sex, state, race,total) 
) unpvt
)t
group by data,info -- with rollup
order by data;

Unpivoted output

fiddle

It seems the solution here is just a simple UNPIVOT and I was struggling to wrap my head around/conceptualize it. I think needing the percentage was making this seem more complicated than it needed to be.

None of these answers really got me to the result I needed, however this is what I ended up using which generates the result table in my question perfectly. I’ll wait a little bit to see if a better solution is posted before accepting this answer.

WITH upvt AS (
    SELECT *
    FROM (
        SELECT
            COUNT(ud.userid) AS Total
            , SUM(CASE WHEN ud.sex = 'M' THEN 1 END) AS Male
            , SUM(CASE WHEN ud.sex = 'F' THEN 1 END) AS Female
            , SUM(CASE WHEN ud.sex = 'D' THEN 1 END) AS Declined
            , SUM(CASE WHEN ud.state = 'tx' THEN 1 END) AS [In State]
            , SUM(CASE WHEN ud.state <> 'tx' THEN 1 END) AS [Out State]
            , SUM(CASE WHEN ud.race = 'B' THEN 1 END) AS Black
            , SUM(CASE WHEN ud.race = 'W' THEN 1 END) AS White
            , SUM(CASE WHEN ud.race = 'H' THEN 1 END) AS Hispanic
            , SUM(CASE WHEN ud.race = 'A' THEN 1 END) AS Asian
        FROM UserData ud
    ) p
    UNPIVOT (
        Totals FOR Category IN (Total, Male, Female, Declined, [In State], [Out State], Black, White, Hispanic, Asian)
    ) AS up
)
SELECT 
    u.Category
    , u.Totals
    , (CAST(u.Totals AS FLOAT) / CAST(u2.Totals AS FLOAT)) * 100 AS Percentage
FROM upvt u
CROSS JOIN (
    SELECT * FROM upvt WHERE Category = 'Total'
) u2

SQL Fiddle