SQL Dynamic Pivot replace NULLs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Dynamic Pivot replace NULLs

Author

Topic

redline9k
Starting Member

4 Posts

Posted - 2012-01-31 : 10:58:04

My pivots work fine, but I cant figure out where to stick an ISNULL(), and Ive already tried quite a few combinations with no luck;


declare @columns varchar(MAX)

SELECT @columns = COALESCE(@columns + ',[' + cast(ex_s2 as varchar) +']',
'['+ cast(ex_s2 as varchar) +']')
FROM eval_loans_data_4449
GROUP BY ex_s2
ORDER BY ex_s2 DESC

declare @stringSQL as varchar(MAX)

SET @stringSQL = 
'
select *
FROM(

	SELECT count(0) as LCount, delinquent_months, ex_s2 as Jud_Ind
	FROM eval_loans_data_4449
	GROUP BY delinquent_months, ex_s2
    ) x
pivot(sum(LCount) for [Jud_Ind] in (' + @columns + ')) as dcnt
ORDER BY delinquent_months
'

EXECUTE(@stringSQL)

redline9k
Starting Member

4 Posts

Posted - 2012-01-31 : 11:12:56

changed SELECT count(0) to SELECT 1 as cnt, then changed sum(LCount) to be count(cnt)

empty count will show 0, empty sum will show NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 13:21:39

stick ISNULL or COALESCE over sum fields to make them 0 if NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources