Geek asks and answers

ROLLUP function; Replace NULL with “Total” with the data type of the INT column not VARCHAR

I'm having trouble replacing my ROLLUP NULL with a string value, because my column data type is Integer.

SELECT CASE
WHEN GROUPING(Column1) = 1 THEN 'Total'
ELSE Column1
END  Column1, SUM(Column2) AS MySum
FROM MyTable
GROUP BY Column1 WITH ROLLUP;

I can put a numerical value in:

WHEN GROUPING(Column1) = 1 THEN '9999'

but I can’t figure out how to convert to varchar if the value is NULL and then replace it with “Total”.

+3
source share
1 answer

Test Data

DECLARE @MyTable TABLE (Column1 INT,Column2 INT)
INSERT INTO @MyTable VALUES
(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3)

SELECT CASE
         WHEN GROUPING(Column1) = 1 THEN 'Total'
         ELSE CAST(Column1 AS VARCHAR(10))     --<-- Cast as Varchar
       END  Column1
      , SUM(Column2) AS MySum
FROM @MyTable
GROUP BY Column1 
WITH ROLLUP;

Result set

╔═════════╦═══════╗
║ Column1 ║ MySum ║
╠═════════╬═══════╣
║ 1       ║     6 ║
║ 2       ║     6 ║
║ 3       ║     6 ║
║ Total   ║    18 ║
╚═════════╩═══════╝

Note

The reason you could not do what you tried to do is because when using the operator CASEin each case, the returned data type must be the same.

In the above query, I only have CAST colum1 in varchar and it worked.

+2

More articles:


All Articles