SQL Server将数据透视为百分比而不是值,并添加总计行和列

我有一个小轴心的代码:

SELECT [U C], [M] AS EE, [F] AS LL
FROM   
(SELECT [U N], f_k, [U C]  
FROM [MH2] 
WHERE [U C] IN ('I', 'E', 'T')) p  
PIVOT (COUNT ([U N]) 
 FOR f_k IN ([M], [F])) AS pvt  
ORDER BY pvt.[U C]

输出:

U C     EE      LL
E       509762  412358
I       16440   1138538
T       197     8753

但是,我希望它是这样一个百分比,并且有一个总的行和列:

U C   EE        LL      Total
E     24.44%    19.77%  44.20%
I     0.79%     54.58%  55.37%
T     0.01%     0.42%   0.43%
Total 25.23%    74.77%  100.00%

编辑使用变量@ Gordon Linoff 的答案。

SELECT COALESCE([U C], 'Total') [U C],
SUM(CASE WHEN f_k = 'F' THEN 1 ELSE 0 END) * 100.0 / cnt LL,
SUM(CASE WHEN f_k = 'M' THEN 1 ELSE 0 END) * 100.0 / cnt EE,
COUNT(*) * 100.0 / cnt Total
FROM (SELECT mh2.*, count(*) over () cnt
FROM mh2
WHERE [U C] IN ('I', 'E', 'T') 
AND f_k IN ('F', 'M')) mh2
GROUP BY GROUPING SETS (([U C], cnt))
UNION ALL
SELECT 'Total', SUM(a.LL), SUM(a.EE), ROUND(SUM(a.Total),2)
FROM(
SELECT COALESCE([U C], 'Total') [U C],
SUM(CASE WHEN f_k = 'F' THEN 1 ELSE 0 END) * 100.0 / cnt LL,
SUM(CASE WHEN f_k = 'M' THEN 1 ELSE 0 END) * 100.0 / cnt EE,
COUNT(*) * 100.0 / cnt Total
FROM (SELECT mh2.*, count(*) over () cnt
FROM mh2  
WHERE [U C] IN ('I', 'E', 'T') 
AND f_k IN ('F', 'M')) mh2
GROUP BY GROUPING SETS (([U C], cnt))) a