SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Change NULLS to blank in Dynamic Pivot Results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mpadilla2
Starting Member

1 Posts

Posted - 09/11/2013 :  16:19:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 09/11/2013 :  17:20:28  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000