Replace Null by 0. Help me! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replace Null by 0. Help me!

Author

Topic

txk2601
Starting Member

19 Posts

Posted - 2009-03-19 : 04:18:48

I have a query by following:

SELECT ShiftID, [1996], [1997], [1998], [1999], [2000], [2001], [2002], [2003]
FROM (
SELECT ShiftID,COUNT(ShiftID) as SumShiftID ,YEAR(ModifiedDate) [YEAR]
FROM AdventureWorks.HumanResources.EmployeeDepartmentHistory
GROUP BY ShiftID,YEAR(ModifiedDate)
) As P
PIVOT
(
AVG(SumShiftID)
FOR [YEAR] IN ([1996], [1997], [1998], [1999], [2000], [2001], [2002], [2003])
) AS PivotTable

Result is a table with Null value in the Row. I wan't replace NULL by 0 (zero). Please help me. Thank a lot

matty
Posting Yak Master

161 Posts

Posted - 2009-03-19 : 04:21:52

USE ISNULL function like ISNULL( [1996],0)

txk2601
Starting Member

19 Posts

Posted - 2009-03-19 : 04:47:47

OK, thanks matty. Now, I wan't SELECT ShiftID, [1996], [1997], [1998],... is dynamic (auto generate when new row was inserted in table). I don't know make it. Please help me!

matty
Posting Yak Master

161 Posts

Posted - 2009-03-19 : 04:57:30

Do you mean YEAR is dynamic? If so, you have to use dynamic sql.

txk2601
Starting Member

19 Posts

Posted - 2009-03-19 : 04:58:59

Yes, Year is dynamic. Can't you help me more about dynamic sql? Thanks!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-19 : 05:12:06

Here Dynamic Sql syntax

declater @fieldname varchar(100)

declare @str varchar(2000)

set @fieldname ='1990'

set @str="Select isnull("+@fieldname+",0) from tablename"

exec (@str)

Regards

Senthil.C
----------------------------------------------------------------------------
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

txk2601
Starting Member

19 Posts

Posted - 2009-03-19 : 05:15:13

OK, thanks senthil_nagore very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources