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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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  
 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.04 seconds. Powered By: Snitz Forums 2000