ISNULL fUNCTION

resultSet.xls


Hi all,
I have simple requirement, wanting to replace the Null Values with '-' in my code, please find the attachment. I know this could be achieved by ISNULL function, not sure  where exactly to implement in the  code.
Also want to  create a new table from this result set. Something like Select * into [tablename] from [oldtablename]

Hope am clear.

Any suggestions are highly appreciated. resultSet.xls
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols + 
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate,Disposition from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

parpaaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
updated T-SQL
declare @cols1 nvarchar(max)
declare @cols2 nvarchar(max)
 select  @cols1 = stuff(( select distinct top 100 percent ',isnull([' + convert(varchar,t2.Calldate,105) + '],''-'') [' + convert(varchar,t2.Calldate,105) + ']'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''

 select  @cols2 = stuff(( select distinct top 100 percent ',[' + convert(varchar,t2.Calldate,105) + ']'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''
declare @query nvarchar(max)
set @query = 'select [Mobile no],' + @cols1 + ' 
from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols2 + ')) as p'
print @query
exec (@query)

Open in new window

0
 
parpaaAuthor Commented:
'REPLACE FN ON DISPOSITION COLUMN'

THANKS
0
 
parpaaAuthor Commented:
SORRY i MEANT ISNULL FUNCTION
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pratima PharandeCommented:
You can use case also like this

 declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then '-' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
 
Pratima PharandeCommented:
Using ISNULL

 declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, ISNULL(Disposition , '-') AS Disposition   from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
 
parpaaAuthor Commented:
Msg 8117, Level 16, State 1, Line 9
Operand data type nvarchar is invalid for subtract operator.
0
 
parpaaAuthor Commented:
Pratima,

It is not necessary that I have to go for '-' sign, any other character will do.
Hope am clear
0
 
parpaaAuthor Commented:
@ Pratima

I tried with ISNULL Function, throwing me error.


Msg 8117, Level 16, State 1, Line 8
Operand data type nvarchar is invalid for subtract operator.
0
 
Pratima PharandeCommented:
try this

declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, ISNULL(Disposition ,
"-") AS Disposition   from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
 
parpaaAuthor Commented:
No Luck :(
Msg 207, Level 16, State 1, Line 2
Invalid column name '-'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

0
 
parpaaAuthor Commented:
@Thanks Angel for your suggestion.

Unfortunately there is no change I still see the null values.
0
 
Pratima PharandeCommented:
try this

declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, ISNULL(Disposition ,
'''-''') AS Disposition   from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols + ')) as p'
exec (@query)
0
 
parpaaAuthor Commented:
Msg 8117, Level 16, State 1, Line 9
Operand data type nvarchar is invalid for subtract operator.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
declare @cols nvarchar(2000)
 select  @cols = stuff(( select distinct top 100 percent '],[' + convert(varchar,t2.Calldate,105)
                           from    [Source table] as t2
                          order by '],[' + convert(varchar,t2.Calldate,105)
                            for xml path('')), 1, 2, '') + ']'
declare @query nvarchar(4000)
set @query = 'select [Mobile no],' + @cols +
               'from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End) for Calldate in (' + @cols + ')) as p'
exec (@query)

Open in new window

0
 
parpaaAuthor Commented:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'convert'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, actually: is the column value NULL or empty string?
please
0
 
parpaaAuthor Commented:
@Angel,

Please find the attachment of my Source table content.
Hope you get an idea.
Thnk you
Source-Table.xlsx
0
 
Kevin CrossChief Technology OfficerCommented:
The issue is you are pivoting, IMHO, so the NULLs are when a particular row has a column after pivot for which it didn't actually have a row/record of data; therefore, wrapping the original query with ISNULL does nothing as you are finding.

To fix, you need to generate a second listing of volume that has definitions like:
'ISNULL('+col1+',''-'') as '+col1

Not on a full keyboard, but hopefully you get the drift.
0
 
Kevin CrossChief Technology OfficerCommented:
Volume = columns
I.e., if the second variable/listing of column names is @colsSel then you would just replace @cols in the select portion of the query with @colsSel. The instance of @cols in the pivot clause would stay the same.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here is my testing code.
I see that the isnull() has to go like this:
create table  [Source table] ( [Mobile No] varchar(100),	calldate datetime, 	Disposition	varchar(10), HUB varchar(10) )
go
set dateformat dmy
go

insert into [Source table] values('423432657','12/09/2010 00:00','PTP','KK')
insert into [Source table] values('12345823','13/09/2010 00:00','PTP','KK')
insert into [Source table] values('2395586','14/09/2010 00:00','PTP','KK')
insert into [Source table] values('23902334','15/09/2010 00:00','PTP','KK')
insert into [Source table] values('34853433','16/09/2010 00:00','PTP','KK')
insert into [Source table] values('2380334','17/09/2010 00:00','PTP','KK')
insert into [Source table] values('32323854','18/09/2010 00:00','PTP','KK')
insert into [Source table] values('23823844','19/09/2010 00:00','PTP','KK')
insert into [Source table] values('2380232','20/09/2010 00:00','NB','KK')
insert into [Source table] values('83235671','21/09/2010 00:00','NB','KK')
insert into [Source table] values('84235671','22/09/2010 00:00','NB','AP')
go

declare @cols1 nvarchar(max)
declare @cols2 nvarchar(max)
 select  @cols1 = stuff(( select distinct top 100 percent ',isnull([' + convert(varchar,t2.Calldate,105) + '],''-'')'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''

 select  @cols2 = stuff(( select distinct top 100 percent ',[' + convert(varchar,t2.Calldate,105) + ']'
                           from    [Source table] as t2
                          order by 1
                            for xml path('')), 1, 1, '') + ''
declare @query nvarchar(max)
set @query = 'select [Mobile no],' + @cols1 + ' 
from (select [Mobile no],convert(varchar,CallDate,105) CallDate, Case When Disposition is NULL then ''-'' Else Disposition End as Disposition  from [Source table]) t1
               pivot (max(Disposition) for Calldate in (' + @cols2 + ')) as p'
print @query
exec (@query)
go
go
select * from [Source table]
go
drop table [source table]

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Exactly, a3...note, you will want to repeat the column value piece or you will end up with an unnamed column in the final selection since the is null test being an expression needs to be aliased.
0
 
parpaaAuthor Commented:
That worked perfectly fine. Thanks @Angel

BTW The column names have no name: the column name is supposed to be dates.
Please refer to my resultset file attached.
resultSet.xls
0
 
parpaaAuthor Commented:
Thanks again :)
0
 
Kevin CrossChief Technology OfficerCommented:
Glad we were able to help you!
Best regards and happy coding,
Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.