Click here to Skip to main content
15,943,294 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi have nulls in data set that I'm trying to pivot with a total column and hence my total column is coming out as null. My code is as follows

DECLARE @cols       NVARCHAR(MAX)=''
DECLARE @query      NVARCHAR(MAX)=''
DECLARE @COLS_SUM   NVARCHAR(MAX)=''

--Preparing columns for Pivot
SELECT @cols = @cols + QUOTENAME(finmonth) + ',' 
FROM (SELECT DISTINCT finmonth FROM [DATASOURCE] ) AS tmp
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

--Preparing sum of columns for Totals Horizontal
SELECT @COLS_SUM = @COLS_SUM + QUOTENAME(finmonth) + '+' 
FROM (SELECT DISTINCT finmonth FROM [[DATASOURCE] ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'


SET @query = 
            'SELECT *'+@COLS_SUM+'  INTO #TAB FROM 
            (
                    SELECT var1, var2, var3, finmonth, sum(Actual_Activity) as [activity]
  FROM [[DATASOURCE]
  where [FinYear] = ''2018''
    group by  var1, var2, var3, finmonth
            ) src
            PIVOT 
            (
                   SUM([activity])  FOR finmonth IN (' + @cols + ')
            ) piv

            SELECT * FROM #TAB
            

            '

execute(@query)


Here is some example dummy data which would result in the issue. So where one Var1 (var1=a in below) has data for finmonth=2 but another doesn't. Var1 = b will have null for finmonth = 2 as they have no data for this month.
var1	var2	var3	finmonth	Actual_activity
a	b	c	1	20
a	b	c	1	30
a	b	c	1	40
a	b	c	2	10
a	b	c	2	20
a	b	c	2	30
b	b	c	1	1
b	b	c	1	2
b	b	c	1	3


The results table looks like this - but total for 2nd row should be 6
r1	var2	var3	1	2	Total
a	b	c	90	60	150
b	b	c	6	Null	Null


What I have tried:

I've tried using isnull in the main group by query but seems to have no effect
Posted
Updated 29-Dec-22 17:24pm
v4
Comments
CHill60 8-Apr-19 8:10am    
Some sample data would be nice. Instead of showing us the dynamic SQL why not present an example of the actual SQL that is causing you a problem - that way we don't have to try to recreate quite so much
Member 14219614 8-Apr-19 8:18am    
Thanks for your comment, I've added some dummy data which would result in issue described. Sorry I am new to dynamic SQL so question might not be phrased very well
CHill60 9-Apr-19 3:37am    
Sorry I didn't come back to you on this but at least Maciej was able to furnish you with a good solution :-)
[no name] 8-Apr-19 10:35am    
Well, SHOW where / how you used ISNULL that "seems" to have no effect.

https://database.guide/how-to-replace-null-with-another-value-in-sql-server-isnull/
Member 14219614 8-Apr-19 10:48am    
Please see the updated dummy data and explanation above the data table

1 solution

A static version of sql statement should look like:

SQL
SELECT var1, var2, var3, [1], [2], COALESCE([1], 0) + COALESCE([2], 0) AS Total
FROM (
    SELECT var1, var2, var3, finmonth, Actual_Activity
    FROM YOUR_TABLE
    WHERE FinYear = 2018) AS DT
PIVOT(SUM(Actual_Activity) FOR finmonth IN ([1], [2])) AS PVT


Now, you have to "convert it" into dynamic version.
If you would like to avoid NULL's in [1] and [2] cols, you can use COALESCE method around them.

Note: FinYear and finmonth should be numeric fields!

[EDIT]
If you would like to get correct data in a @COLS_SUM variable, check this:
SQL
SELECT @COLS_SUM = @COLS_SUM + 'COALESCE(' + QUOTENAME(finmonth) + ',0)+' 
FROM (SELECT DISTINCT finmonth FROM YOUR_TABLE ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'
 
Share this answer
 
v4
Comments
Member 14219614 8-Apr-19 15:02pm    
Thanks for your reply, I see that I had a redundant aggregation in my query and is good to correct that. However, what you've suggested seems to be equivalent to my original code in that still get nulls in the pivoted fields which result in the grand total column having nulls.

To correct this the first line select statement should be SELECT var1, var2, var3, [1], [2], isnull([1],0) + isnull([2],0) AS Total

And this is really the crux of my original question - I don't know how to convert this to dynamic format
Maciej Los 8-Apr-19 15:19pm    
Check updated answer.
Member 14219614 8-Apr-19 15:24pm    
Thank you Maciej Los, that now works perfectly
Maciej Los 8-Apr-19 15:30pm    
You're very welcome!
CHill60 9-Apr-19 3:37am    
5'd

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900