1

I thought the simple query below would let me convert a Null to a 0, but it doesn't do the conversion. I have a bunch of Null results everywhere.

SELECT *
FROM (SELECT [Level 1], [Level 2], [Level 3], Headcount, IsNull([ME],0) AS ME
FROM Personnel_Raw) 
SCR 
PIVOT 
(SUM(Headcount) FOR ME IN (ME1, ME2, ME3, ME4, ME5, ME6, ME7, ME8, ME9, ME10, ME11, ME12, ME13, ME14, ME15)) 
PIV;

I guess the IsNull is not firing. Or, maybe it's converted into a 0 and then converted back into a Null.

halfer
  • 19,824
  • 17
  • 99
  • 186
ASH
  • 20,759
  • 19
  • 87
  • 200
  • Ah! This is not a duplicate! I did a Google search before posting and I saw that example and tried it and it didn't work for me. I'm using SQL Server 2014, if that makes any difference. – ASH Jul 08 '16 at 14:45
  • Can you update the question with some sample data and the attempt that failed then so we can see how it differs from the one marked as duplicate? If it indeed is different I'll reopen it. – jpw Jul 08 '16 at 14:52
  • The script below works, but it seems overly complex. Is there a more efficient way of doing this?? Select [Level 1], [Level 2], [Level 3], isnull(ME1,0) As ME1, isnull(ME2,0) As ME2, isnull(ME3,0) As ME3, isnull(ME4,0) As ME4, isnull(ME5,0) As ME5, isnull(ME6,0) As ME6, isnull(ME7,0) As ME7, isnull(ME8,0) As ME8, isnull(ME9,0) As ME9, isnull(ME10,0) As ME10, isnull(ME11,0) As ME11, isnull(ME12,0) As ME12, isnull(ME13,0) As ME13, isnull(ME14,0) As ME14, isnull(ME15,0) As ME15 From ( SELECT * etc, etc, etc – ASH Jul 08 '16 at 14:59
  • As it worked I guess the question was indeed a duplicate and won't reopen, but as you say the solution might seem complex, but I don't think there is any better way to do it. – jpw Jul 08 '16 at 15:20

0 Answers0