CODEWITHSUNDEEP
t-sqlpivot-table
ironcurtain
ironcurtain

Reputation: 690

Change nulls to zeros (pivot table)

I have created stored procedure which creates pivoted table as below. The problem is that it doesn't work as expected because it doesn't change nulls to zeros. Could You please show me the right direction?

create procedure sp_system_counts
as

declare @columns nvarchar(max)
declare @columnscondition nvarchar(max)
declare @query nvarchar(max)

select distinct systemgroup, systemgroupsortorder into #temp_table_system_group
from systemgroups
where systemgroup not like 'N/A'
order by systemgroupsortorder

select @columns = isnull(@columns + ',', '') + '[' +  convert(varchar, systemgroup) + ']' FROM #temp_table_system_group

select @columnscondition =  + isnull(@columnscondition + ' or ', '') + '[' +  convert(varchar, systemgroup) + '] <> 0' FROM #temp_table_system_group
--select @columns

create table #temp_systems (
    systemid int,
    systemnane varchar(max),
    region varchar(50),
    systemgroup varchar(50),
    remsid int,
    remscode int)

insert into #temp_systems

select distinct sy.systemid, sy.systemname, co.region, syg.systemgroup, re.remsid, re.remscode
from systems sy
    inner join servers se on sy.systemid = se.systemid and sy.systemid = sy.systemid
    inner join rems re on se.remsid = re.remsid
    inner join cities ci on re.cityid = ci.cityid
    inner join countries co on ci.countryid = co.countryid
    inner join systemmodels sym on sy.systemmodelid = sym.systemmodelid
    inner join systemtypes syt on sym.systemtypeid = syt.systemtypeid
    inner join systemgroups syg on syt.systemgroupid = syg.systemgroupid
    where syg.systemgroup not like 'N/A'
order by syg.systemgroup

set @query = '
    select region, ' + @columns + '
    from (
        select distinct region, systemgroup, cnt = isnull(count(systemid),0) from #temp_systems
        group by region, systemgroup
        with rollup) p
            pivot (sum (cnt) for systemgroup in (' + @columns + ')) as asd
            where (' + @columnscondition +')' 

execute(@query)

drop table #temp_table_system_group
drop table #temp_systems

Upvotes: 0

Views: 166

Answers (1)

i-one
i-one

Reputation: 5120

I'm not sure, about logic implemented here, but may be it will help, if you change:

select @columns = isnull(@columns + ',', '') + '[' +  convert(varchar, systemgroup) + ']' FROM #temp_table_system_group
select @columnscondition =  + isnull(@columnscondition + ' or ', '') + '[' +  convert(varchar, systemgroup) + '] <> 0' FROM #temp_table_system_group

to

select @columns = isnull(@columns + ',', '') + 'isnull(' + quotename(convert(varchar, systemgroup)) + ', 0) as ' + quotename(convert(varchar, systemgroup)) FROM #temp_table_system_group
select @columnscondition =  + isnull(@columnscondition + ' or ', '') + 'isnull(' + quotename(convert(varchar, systemgroup)) + ', 0) <> 0' FROM #temp_table_system_group

i.e. doing isnull(.., 0) over pivoted data, not before pivoting

Upvotes: 1

Related Questions