Change NULLS to blank in Dynamic Pivot Results

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)

Change NULLS to blank in Dynamic Pivot Results

Author

Topic

mpadilla2
Starting Member

1 Post

Posted - 2013-09-11 : 16:19:58

Subject says it all. I would like to change the NULLS to blank in my Dynamic Pivot results.

Here is a small sample of data from my DistinctPages table:


pageno      groupid    customized
----------- ---------- ----------
101105      cshaffre   x
105101      cshaffre   x
105110      cshaffre   x
122566      cshaffre   x
100100      darryl     x
101100      darryl     x
103800      darryl     x
100110      darryl     x
122201      dbouma     x
101151      dbouma     x
107102      dbouma     x
104102      dbouma     x
105121      dbouma     x

Here is the dynamic code I have:


DECLARE @query VARCHAR(4000), @groupids VARCHAR(8000)
SELECT @groupids = STUFF(( SELECT DISTINCT 
	'],[' + LTRIM(groupid) 
	FROM DistinctPages 
	ORDER BY '],[' + LTRIM(groupid)
	FOR XML PATH('')
	), 1,2, '') + ']'	
SET @query =
'SELECT * FROM (SELECT pageno, groupid, customized FROM DistinctPages)t
PIVOT (MAX(customized) FOR groupid
IN ('+@groupids+')) AS CustomizedPagesPerGroups'
EXECUTE (@query)

Here is a small sample of my pivot table results:


pageno      cshaffre darryl dbouma dsanders eliteadm FIRM french Gerry Ilee Irene lgentry matt mduran Mine mpadilla mwilton pmp  rlandsin spanish sverne swachman train train01 vicky vlad VTest vvargas ymalluf
----------- -------- ------ ------ -------- -------- ---- ------ ----- ---- ----- ------- ---- ------ ---- -------- ------- ---- -------- ------- ------ -------- ----- ------- ----- ---- ----- ------- -------
100100      NULL     x      x      x        x        x    x      x     x    NULL  x       x    NULL   NULL x        x       NULL x        x       x      x        x     x       x     x    x     x       NULL
100101      NULL     NULL   NULL   NULL     x        x    x      NULL  NULL NULL  NULL    NULL NULL   NULL NULL     NULL    x    x        x       NULL   NULL     NULL  NULL    NULL  x    NULL  NULL    NULL
100102      NULL     NULL   x      NULL     x        x    x      x     x    NULL  x       x    NULL   NULL x        NULL    NULL x        x       x      x        x     NULL    NULL  NULL NULL  x       NULL
100104      NULL     NULL   NULL   NULL     NULL     NULL x      NULL  NULL NULL  NULL    NULL NULL   NULL NULL     NULL    NULL NULL     x       NULL   NULL     NULL  NULL    NULL  NULL NULL  NULL    NULL
100110      NULL     x      x      NULL     NULL     NULL x      NULL  NULL NULL  NULL    NULL NULL   NULL NULL     NULL    NULL NULL     x       NULL   NULL     NULL  NULL    NULL  NULL NULL  x       NULL
100113      NULL     NULL   x      NULL     NULL     NULL x      NULL  NULL NULL  NULL    NULL NULL   NULL NULL     NULL    NULL NULL     x       NULL   NULL     NULL  NULL    NULL  NULL NULL  NULL    NULL
100115      NULL     NULL   NULL   NULL     NULL     NULL x      NULL  x    NULL  NULL    NULL NULL   NULL NULL     NULL    NULL NULL     x       NULL   NULL     NULL  NULL    NULL  NULL NULL  x       NULL

I would like to replace the NULLs you see with a blank or some other text that makes it easier to read.

I was looking into ISNULL and COALESCE but I have not been able to implement it correctly.

Can someone take a look at my code and give me an example of how to do what I need with either ISNULL or COALESCE? Please let me know if you need anything else.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-11 : 17:20:28

Try this - if it doesn't work, replace the EXECTUE (@query) with PRINT @query and take a look at it to see what is wrong (or post that to the forum)

DECLARE @query VARCHAR(4000), @groupids VARCHAR(8000), @colIds varchar(8000);
SELECT @groupids = STUFF(( SELECT DISTINCT 
	'],[' + LTRIM(groupid) 
	FROM DistinctPages 
	ORDER BY '],[' + LTRIM(groupid)
	FOR XML PATH('')
	), 1,2, '') + ']'
	
SELECT @colIds = STUFF(( SELECT DISTINCT 
	',COALESCE(' + QUOTENAME(LTRIM(groupid)) + ','') AS ' + QUOTENAME(LTRIM(groupid)) + ' '
	FROM DistinctPages 
	ORDER BY '],[' + LTRIM(groupid)
	FOR XML PATH('')
	), 1,1, '') 
		
SET @query =
'SELECT pageno, ' + @colIds + ' FROM (SELECT pageno, groupid, customized FROM DistinctPages)t
PIVOT (MAX(customized) FOR groupid
IN ('+@groupids+')) AS CustomizedPagesPerGroups'
EXECUTE (@query)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources