Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

convert NULL to 0 in Pivot Result Set Expand / Collapse
Author
Message
Posted Monday, January 25, 2010 9:56 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 30, 2010 7:19 AM
Points: 246, 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?
Post #853121
Posted Monday, January 25, 2010 12:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #853216
Posted Monday, January 25, 2010 4:01 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 30, 2010 7:19 AM
Points: 246, 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)


Post #853369
Posted Monday, January 25, 2010 4:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #853377
Posted Tuesday, January 26, 2010 8:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 30, 2010 7:19 AM
Points: 246, 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
Post #853707
Posted Tuesday, January 26, 2010 8:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 30, 2010 7:19 AM
Points: 246, 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.
Post #853714
Posted Tuesday, January 26, 2010 9:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #853738
Posted Tuesday, January 26, 2010 9:28 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 30, 2010 7:19 AM
Points: 246, 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.
Post #853754
Posted Tuesday, January 26, 2010 11:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #853836
Posted Wednesday, January 27, 2010 7:02 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 8, 2011 1:45 PM
Points: 5, 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
Post #854353
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse