I'm having some problems replacing my ROLLUP NULL with a string value because my column data type is an 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 numeric value in:
WHEN GROUPING(Column1) = 1 THEN '9999'
but I can't figure out how to convert to varchar if value is NULL and then replace with 'Total'.
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 couldnt do what you were trying to do is because when you use a CASE
statement in each case the returned datatype should be the same.
In above query I have just CAST the colum1 to varchar and it worked.