January 25, 2010 at 9:56 am
I have a process where in the end I show my result in a "Pivot way".
My last result is something like this:
IdTagIdDataColumn1Column2
147Data1NULL37661
148Data25689NULL
149Data3NULLNULL
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?
January 25, 2010 at 12:09 pm
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.
January 25, 2010 at 4:01 pm
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)
January 25, 2010 at 4:35 pm
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.
January 26, 2010 at 8:37 am
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
ClientIdint
Advarchar(10)
TagIDint
Namevarchar(10)
Totalint
This will be the data in the Table
ClientIdAdTagIdNameTotal
1D147American1000
1D247American500
1D347American300
1D448Hispanic1000
1D548Hispanic200
This is the result when I pivot the table
ClientIdTagIdNameD1D2D3D4D5
147American1000500300NULLNULL
148HispanicNULLNULLNULL1000200
You can copy and paste this sample in Sql to create the Scenario:
create table #TestTable
(
ClientIdint
,Advarchar(10)
,TagIDint
,[Name]varchar(10)
,Totalint
)
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
January 26, 2010 at 8:46 am
NOTE: I cant use the IsNull or the COALESCE, because my columns always will be different. they can be between 1 to 15.
January 26, 2010 at 9:14 am
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:
ClientidTagIdNameD1D2D3D4D5
147American100050030000
148Hispanic0001000200
*/
January 26, 2010 at 9:28 am
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.
January 26, 2010 at 11:11 am
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.
January 27, 2010 at 7:02 am
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
(@parAmountDecimal(10,2) = 0.00)
Returns Decimal(10,2) As
Begin--udfNullToZero
--=======================================================================
--Declare Local Variables
Declare@locReturnValueDecimal(10,2)
Declare @locErrorCodeInt
--=======================================================================
--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
January 27, 2010 at 9:16 am
RHaverty 8478 (1/27/2010)
This is a function I use to return 0.00 for a null value. The use isDeclare Amt Decimal(10,2)
Select Amt = dbo.ConvertNullToZero(DbAmtVal)
From SomeTable
What is the advantage of your function compared to the SQL Server standard ISNULL() function?
Did you do a performance comparison on large tables? If so, would you mind sharing the results.
Btw: How would you incorporate your function into the given scenario other than replacing ISNULL() with dbo.ConvertNullToZero()?
January 27, 2010 at 9:37 am
The tables I am using this particular function with:
Check_Main - 132,937 records
Check_Dates - 138,232 records
Check_Header - 10,469,471 records
Check_Detail - 52,976,023 records
Builds a reporting table for 12 months of data (7,765 record result) in less than 30 seconds when running by itself - less that 1.5 minutes when there is moderate contention like significant data loads on these tables. I feel that the use of functions provides clearer code & readability plus the added advantage of coding functional "objects or classes" that I can reuse in my proc stacks.
I have not been able to determine if the are any significant performance advantages in any of my function use over SQL functionality but it certainly provides clean code, readability and simple reuse of code.
January 27, 2010 at 11:55 am
Thank you for the advices.
Lutz solution works fine for what Im doing right now. The set of results that I need arent big, just the number of columns change.
Thank you both
August 11, 2011 at 5:52 am
SELECT
Isnull(pi.april,0) as April
,Isnull(pi.may,0) as May
,Isnull(pi.june,0)as June
,Isnull(pi.July,0)as July
,Isnull(pi.april+pi.may+pi.june+pi.july,0)as total
FROM(
SELECT
STATUS,YEAR(DATEOFFICE) [Year],
Convert(varchar(12)
,DateName(MM,Dateoffice),100) as Month
,leaveamount
FROM TBLEMPLOYEE TBL_Emp
,TBLTIMEREGISTER Tbl_Time
where tbl_Emp.paycode=tbl_time.paycode and
status
NOT in('A','P','HLD','WO', 'MIS','OD','CLOD', 'POW')
) WorkOrders
PIVOT
(
SUM(LEAVEAMOUNT)
FOR
[Month] IN (
[April],[May],[June]
,[July]
)
) AS Pi
ORDER BY [Year]
November 26, 2012 at 2:38 am
I want the rollup of D1, D2 etc for each name.
Example: If the result set like the below..
Clientid TagId NameD1D2D3D4D5
147American100050030000
148Hispanic0001000200
147American100050030000
148Hispanic0001000200
I want the result set like the following..
Clientid TagId NameD1D2D3D4D5
147American100050030000
147American100050030000
147 ALL 2000 1000 600 0 0
148Hispanic0001000200
148Hispanic0001000200
148 ALL 0 0 0 2000 400
PLEASE HELP ME OUT
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply