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)
Go to Top of Page
   

- Advertisement -