insert into userdata values

('usr1','M','PA','B')

,('usr2','F','TX','W')

,('usr3','M','TX','B')

,('usr4','D','NE','H')

,('usr5','F','TX','A')

;

select * from userdata;

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