我目前有一个脚本,使用 rollup 聚合一些数据:
SELECT
CASE
WHEN GROUPING(Custodian) = 1
THEN 'Grand Total'
WHEN GROUPING(PortfolioID) = 1
THEN Custodian+''+'Total'
ELSE Custodian
END AS Custodian
, PortfolioID
, PortfolioBaseCCY
, [Date]
, SUM(AmountTotalBaseEquiv) AS AmountTotalBaseEquiv
, ExchangeRate
, AmountTotalBaseEquivUSD
, PortfolioNAVUSD
, SUM(TotalCashPctNAV) AS TotalCashPctNAV
FROM @ResultSet
WHERE TotalCashPctNAV > 5
GROUP BY Custodian
, PortfolioID
, PortfolioBaseCCY
, [Date]
, AmountTotalBaseEquiv
, ExchangeRate
, AmountTotalBaseEquivUSD
, PortfolioNAVUSD
, TotalCashPctNAV WITH ROLLUP
HAVING GROUPING_ID(Custodian
, PortfolioID
, PortfolioBaseCCY
, [Date]
, AmountTotalBaseEquiv
, ExchangeRate
, AmountTotalBaseEquivUSD
, PortfolioNAVUSD
, TotalCashPctNAV) IN (1,255,511)
ORDER BY CASE WHEN GROUPING(Custodian) = 1 THEN 2 ELSE 1 END, Custodian, TotalCashPctNAV DESC, PortfolioID
这会返回类似以下示例的数据:
Custodian PortfolioID PortfolioBaseCCY Date AmountTotalBaseEquiv ExchangeRate AmountTotalBaseEquivUSD PortfolioNAVUSD TotalCashPctNAV
XXXX TEST USD 11/09/2012 85708860.21 1 85708860.21 370253861.3 23.15
XXXX Total NULL NULL NULL 85708860.21 NULL NULL NULL 23.15
ZZZZ TEST1 GBP 11/09/2012 48427.91 0.6225 77795.84 77795.84 100
ZZZZ TEST2 GBP 11/09/2012 7772.61 0.6225 12486.12 12486.12 100
ZZZZ TEST3 USD 11/09/2012 1832627.81 1 1832627.81 17343500.68 10.56
ZZZZ Total NULL NULL NULL 1888828.33 NULL NULL NULL 210.56
Grand Total NULL NULL NULL 310273031.4 NULL NULL NULL 1051.71
我希望空值变成“”,这样在该行上只有总标签和两个求和总数,这可行吗?