Remove zeros from totals / totals in tsql drive

Question

I currently have a script that aggregates some data using a drive:

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 

This returns data, such as:

 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 

I would like NULLS to become ``, so that only the Total label and the two summed totals are the only bits of data on this particular row, is this possible?

Answer 1

If you are ok with NULL values ​​displayed as empty rows in both data rows and full rows, you can convert all non-row columns to rows and use COALESCE (or ISNULL), for example:

 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 

Otherwise, you can use the case expression, as with Custodian, to determine which rows are totals. For instance:

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

Answer 2

You can use the ISNull () function in SQL to replace NULL with a space like this:

 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