在T-SQL的ROLLUP中移除子总计/总计中的空值

Question

我目前有一个脚本，使用 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

我希望空值变成“”，这样在该行上只有总标签和两个求和总数，这可行吗？

- chrissy p

Answer 1

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

您可以在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 

- Michael Brown

Answer 2

如果您可以接受在数据行和总行中将 NULL 值显示为空字符串，那么您可以将所有非字符串列转换为字符串，并使用 COALESCE（或 ISNULL）如下：

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

        END AS Custodian 

    ,   COALESCE(PortfolioID,'') AS PortfolioID
    ,   COALESCE(PortfolioBaseCCY,'') AS  PortfolioBaseCCY
    ,   COALESCE(CONVERT(char(10),[Date],101),'') AS [Date]
    ,   SUM(AmountTotalBaseEquiv) AS AmountTotalBaseEquiv 
    ,   COALESCE(CONVERT(char(10),ExchangeRate),'') AS ExchangeRate
    ,   COALESCE(CONVERT(char(20),AmountTotalBaseEquivUSD),'') AS AmountTotalBaseEquivUSD
    ,   COALESCE(CONVERT(char(20),PortfolioNAVUSD),'') AS 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 

否则，您可以使用类似于 Custodian 的 case 表达式来确定哪些行是总计。例如：

CASE WHEN GROUPING(Custodian) + GROUPING(PortfolioId) > 0 THEN '' 
     ELSE PortfolioID END AS PortfolioID

- GilM

---

这个很好用，谢谢。我按照COALESCE的方式操作，因为我们永远不会在非总记录上有空值。感谢你的帮助。 - chrissy p