Change NULLS to blank in Dynamic Pivot Results

• mpadilla2

  SSC Rookie

  Points: 27

  More actions

  September 11, 2013 at 1:44 pm

  #279606

  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.

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 16, 2013 at 6:55 am

  #1650412

  Also place table definition plus some sample data.

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)