SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert NULL to 0 in Pivot Result Set


convert NULL to 0 in Pivot Result Set

Author
Message
MTY-1082557
MTY-1082557
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 502
I have a process where in the end I show my result in a "Pivot way".

My last result is something like this:

Id TagId Data Column1 Column2
1 47 Data1 NULL 37661
1 48 Data2 5689 NULL
1 49 Data3 NULL NULL



I Need the NULL result to show the value 0.

This is my query:

SET @SQL = 'SELECT * FROM
(Select Id,Id,Data, Columns, TOTAL from #SpillReport) SourceData
PIVOT
(min(Total) for Columns in ('+@Columns+')) pivottable
Order by Id, TagId
exec(@sql)



I tried to use the IsNull function in the pivot and it doesnt work. Is anyway to convert the null result to 0 value?
LutzM
LutzM
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38127 Visits: 13559
You have to apply the ISNULL() function to your outer SELECT statement. That means you cannot use a SELECT *.
Unfortunately, your sample data and your query don't match (columns [Columns] and [ TOTAL] are missing, content of @Columns is not defined).
Otherwise we would have been able to show you an example based on your data.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
MTY-1082557
MTY-1082557
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 502
Im sorry. I didnt put the entire code.
I just showed where I thought the IsNull function will be.

This is the code:


declare @columns varchar(max)
declare @sql nvarchar(max)
SET @Columns = substring((select distinct ',['+col1+']' from #Table group by Col1 for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM
(Select id, ZId,TagId,Name, Col1, Total from #SpillReport ) SourceData
PIVOT
(min(Total) for Col1 in ('+@Columns+')) pivottable
Order by ZoneId, TagId '
exec(@sql)



LutzM
LutzM
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38127 Visits: 13559
Please provide all related table definition together with sample data and expected result in a ready to use format.
The query you provided won't run due to missing tables and columns.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
MTY-1082557
MTY-1082557
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 502
I realize that I’m mixing real names with examples. I will show the most important columns.
Please take this as the example:

DataType of the Table to be PIVOT

#TestTable
ClientId int
Ad varchar(10)
TagID int
Name varchar(10)
Total int



This will be the data in the Table

ClientId Ad TagId Name Total
1 D1 47 American 1000
1 D2 47 American 500
1 D3 47 American 300
1 D4 48 Hispanic 1000
1 D5 48 Hispanic 200



This is the result when I pivot the table

ClientId TagId Name D1 D2 D3 D4 D5
1 47 American 1000 500 300 NULL NULL
1 48 Hispanic NULL NULL NULL 1000 200



You can copy and paste this sample in Sql to create the Scenario:

create table #TestTable
(
ClientId int
,Ad varchar(10)
,TagID int
,[Name] varchar(10)
,Total int

)
Insert #TestTable values (1 , 'D1', 47, 'American', 1000)
Insert #TestTable values (1 , 'D2', 47, 'American', 500)
Insert #TestTable values (1 , 'D3', 47, 'American', 300)
Insert #TestTable values (1 , 'D4', 48, 'Hispanic', 1000)
Insert #TestTable values (1 , 'D5', 48, 'Hispanic', 200)


declare @columns varchar(max)
declare @sql nvarchar(max)
SET @Columns = substring((select distinct ',['+Ad+']' from #TestTable group by Ad for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM
(Select Clientid, TagId, [Name], ad, Total from #TestTable ) SourceData
PIVOT
(min(Total) for ad in ('+@Columns+')) pivottable
Order by ClientId, TagId '
exec(@sql)



Same question: How I convert the NULL to value 0 in the Result. When I add it in the SELECT * From, it doesnt reconize it.

Thank you
MTY-1082557
MTY-1082557
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 502
NOTE: I cant use the IsNull or the COALESCE, because my columns always will be different. they can be between 1 to 15.
LutzM
LutzM
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38127 Visits: 13559
Now that we have ready to use data it becomes really easy:
You have to use a dynamic SELECT statement using the ISNULL function inside your dynamic sql statement:
    DECLARE @columns VARCHAR(8000)
DECLARE @columns2 VARCHAR(8000)
DECLARE @sql NVARCHAR(MAX)
SET @Columns = SUBSTRING((SELECT DISTINCT ',['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)
SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+Ad+'],0) AS ['+Ad+']' FROM #TestTable GROUP BY Ad FOR XML PATH('')),2,8000)
SET @SQL = 'SELECT Clientid, TagId, [Name],' + @Columns2 + ' FROM
(Select Clientid, TagId, [Name], ad, Total from #TestTable ) SourceData
PIVOT
(min(Total) for ad in ('+@Columns+')) pivottable
Order by ClientId, TagId '
EXEC(@sql)
/* result set:
Clientid TagId Name D1 D2 D3 D4 D5
1 47 American 1000 500 300 0 0
1 48 Hispanic 0 0 0 1000 200
*/





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
MTY-1082557
MTY-1082557
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 502
OMG!
pretty easy... lol

I was tryint to do the IsNull in one only line, thats why my pivot result always get an error with that.

I see you use the varchar(8000) instead of varchar(max). This save some meory/space ?

Thank you so much.
LutzM
LutzM
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38127 Visits: 13559
Glad it finally worked out! :-)
Did you notice how fast you had a tested solution once you provided ready to use data? ;-)

I see you use the varchar(8000) instead of varchar(max). This save some meory/space ?

If you know that string length will not exceed 8k then you should use varchar(8000) (or even less) instead of varchar(max). Therewith SQL Server "knows" that the value will fit into one page and will store it directly in the data row. Otherwise it will need to store a pointer to the LOB storage space.
If you're unsure regarding performance influence I recommend you try both and compare the performance.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
RHaverty 8478
RHaverty 8478
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 36
This is a function I use to return 0.00 for a null value. The use is

Declare Amt Decimal(10,2)

Select Amt = dbo.ConvertNullToZero(DbAmtVal)
From SomeTable

-- =========================================================================
-- Source: udfNullToZero
-- Version: 1.0.0
-- Author: R Haverty
-- Date: 02/16/2008
-- Revised:
--
-- Function: This User Defined Function accepts a Value and if Null will
-- return 0.00 otherwise it retruns the numeric value
-- -------------------------------------------------------------------------

-- =========================================================================
-- Use the TARS database
Use TARS
Go

-- =========================================================================
IF OBJECT_ID ('dbo.udfNullToZero') IS NOT NULL
DROP Function dbo.udfNullToZero
GO

-- =========================================================================
-- Alter the stored procedure
Create Function [dbo].udfNullToZero
(@parAmount Decimal(10,2) = 0.00)
Returns Decimal(10,2) As

Begin -- udfNullToZero

-- =======================================================================
-- Declare Local Variables
Declare @locReturnValue Decimal(10,2)
Declare @locErrorCode Int

-- =======================================================================
-- Initialize local variable(s)
Set @locReturnValue = 0.00
Set @locErrorCode = 0

-- =======================================================================
-- Test @parAmount to see if it is Null and if it is return 0.00 else
-- return the @parAmount rounded to the nearest dollar.
If @parAmount Is Null
Set @locReturnValue = 0.00
Else
Set @locReturnValue = @parAmount


-- **************************************************************************
-- Return @localReturnValue
Return @locReturnValue
End -- udfNullToZero
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search