SQL Pivot Table Nulls

Question

SQL Pivot Table Nulls

compdude

Valued Member

Points: 51
More actions
November 10, 2015 at 6:10 pm

#399306

Hi Everyone,
I have seen this question posted a few times but could not determine how to apply it to the query statement I have written below. I am looking to have each of the pivot data columns populate with a zero or blank instead of a null for when the data is imported into Excel. Thanks for any help you can offer.
SELECT *
FROM (SELECT [Station_ID], data, [Case_No], [Date], [Account_No], [Last_Name], [First_Name], [Phys_Last], [Phys_First],[Repository], [procedures]
FROM [dbo].[PIVOT_DATA_VIEW]) TEMPDATA PIVOT (max(data) FOR [procedures] IN ([Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7],
[Column8], [Column9], [Column10], [Column11])) PivotTable

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

How about ISNULL? ISNULL(<whatever>,'') for blank values, ISNULL(<whatever>,0) to get a 0.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

compdude Valued Member Points: 51 More actions · Answer 2

So far I have tried isnull for each of the columns but this has not changed the nulls from the pivot data. I believe the isnull needs to take place before those but I am unsure of the syntax. I read other posts where you need to dynamically generate the SQL statement with the isnull but I am unsure where that approach would fit.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

compdude (11/10/2015)
So far I have tried isnull for each of the columns but this has not changed the nulls from the pivot data. I believe the isnull needs to take place before those but I am unsure of the syntax. I read other posts where you need to dynamically generate the SQL statement with the isnull but I am unsure where that approach would fit.

You should try something like this as Alan suggested above.

SELECT [Station_ID],

ISNULL([Column1], 0) AS [Column1]

, ISNULL([Column2], 0) AS [Column2]

, ISNULL([Column3], 0) AS [Column3]

, ISNULL([Column4], 0) AS [Column4]

, ISNULL([Column5], 0) AS [Column5]

, ISNULL([Column6], 0) AS [Column6]

, ISNULL([Column7], 0) AS [Column7]

, ISNULL([Column8], 0) AS [Column8]

, ISNULL([Column9], 0) AS [Column9]

, ISNULL([Column10], 0) AS [Column10]

, ISNULL([Column11], 0) AS [Column11]

FROM

(

SELECT [Station_ID], data, [Case_No], [Date], [Account_No]

, [Last_Name], [First_Name], [Phys_Last], [Phys_First]

,[Repository], [procedures]

FROM [dbo].[PIVOT_DATA_VIEW])

) TEMPDATA

PIVOT (

MAX(data) FOR [procedures] IN (

[Column1], [Column2], [Column3], [Column4]

, [Column5], [Column6], [Column7], [Column8]

, [Column9], [Column10], [Column11]

)

) PivotTable

ISNULL(<YourColumn>) function should be on your SELECT Clause. hope it helps.

compdude Valued Member Points: 51 More actions · Answer 4

That did it. Thanks to both of you for the suggestion. I completely overlooked the fact that to generate the Pivot there is that initial select all. When I tried last time I did not define each of the columns from the pivot in the select that calls the entire pivot. Used your suggestion and that took care of it. Thanks again!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Or try the alternative using cross tabs.

SELECT [Station_ID],

[Case_No],

[Date],

[Account_No],

[Last_Name],

[First_Name],

[Phys_Last],

[Phys_First],

[Repository],

MAX( CASE WHEN [procedures] = 'Column1' THEN data ELSE 0 END) Column1,

MAX( CASE WHEN [procedures] = 'Column2' THEN data ELSE 0 END) Column2,

MAX( CASE WHEN [procedures] = 'Column3' THEN data ELSE 0 END) Column3,

MAX( CASE WHEN [procedures] = 'Column4' THEN data ELSE 0 END) Column4,

MAX( CASE WHEN [procedures] = 'Column5' THEN data ELSE 0 END) Column5,

MAX( CASE WHEN [procedures] = 'Column6' THEN data ELSE 0 END) Column6,

MAX( CASE WHEN [procedures] = 'Column7' THEN data ELSE 0 END) Column7,

MAX( CASE WHEN [procedures] = 'Column8' THEN data ELSE 0 END) Column8,

MAX( CASE WHEN [procedures] = 'Column9' THEN data ELSE 0 END) Column9,

MAX( CASE WHEN [procedures] = 'Column10' THEN data ELSE 0 END) Column10,

MAX( CASE WHEN [procedures] = 'Column11' THEN data ELSE 0 END) Column11

FROM [dbo].[PIVOT_DATA_VIEW]

GROUP BY [Station_ID],