tsql - 从 sql 汇总中的小计/总计中删除空值

Question

我目前有一个脚本，它使用汇总聚合一些数据:

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

我想要的是 NULLS 变成 '' 以便只有 Total 标签和两个总和是该特定行上唯一的数据位，这可能吗？

Answer 1

您可以使用 SQL 中的 ISNull() 函数将 NULL 替换为空格，如下所示:

SELECT 
    CASE 
        WHEN GROUPING(Custodian) = 1 
            THEN 'Grand Total'
        WHEN GROUPING(PortfolioID) = 1
            THEN Custodian+''+'Total'
        ELSE Custodian

    END AS Custodian

,   isNUll(PortfolioID,'')
,   isNull(PortfolioBaseCCY,'')
,   isNull([Date],'')
,   SUM(AmountTotalBaseEquiv) AS AmountTotalBaseEquiv
,   isNull(ExchangeRate,'')
,   isNull(AmountTotalBaseEquivUSD,'')
,   isNull(PortfolioNAVUSD,'')
,   SUM(TotalCashPctNAV) AS TotalCashPctNAV