kcmoore
asked on
Replace Null Cells in Pivot Table
Need assistance with a pivot table format issue. I have the query below that returns a pivot table I export to Access for a end user. I need to replace the 'null' cells in the "week_end_date" columns with either a blank or 0 before I export to Access. Here is my SQL:
/*Active items */
select distinct i.upc,i.description,i.item _size,i.it em_uom
into #i
from dim_item i join dim_item_client ic on i.client_key = ic.client_key
where datediff(dd, i.start_date, '2/24/2007') >= 0
and datediff(dd, i.end_date, '9/2/2006') <= 0
and ic.client_key = 24391
and i.active = 1
and i.country_id = 1
create clustered index [ix_#i] on #i ([upc] asc) on [primary]
/* Active Locations */
select distinct s.customer_id, s.fulldescription, h.description as holding,
d.description as division, b.description as banner, sb.description as subbanner
into #s
from vw_store s
join subbanner sb on s.subbanner_key = sb.subbanner_key
join banner b on sb.banner_key = b.banner_key
join division d on b.division_key = d.division_key
join holding h on d.holding_key = h.holding_key
where datediff(dd, s.start_date, '2/24/2007') >= 0
and datediff(dd, s.end_date, '9/2/2006') <= 0
and s.active = 1
and s.country_id = 1
and b.channel_key = 80
create clustered index [ix_#s] on #s ([customer_id] asc) on [primary]
/*Week ending dates since September 2006 */
select distinct calendar_key, week_end_date
into #w
from calendar
where week_end_date between '9/2/2006' and '2/24/2007'
create clustered index [ix_#w] on #w ([calendar_key] asc) on [primary]
/*Scans for Active,items at Active, US, Locations*/
select s.holding, s.division, s.banner, s.subbanner, s.fulldescription, i.upc, i.description, i.item_size, i.item_uom, w.week_end_date,
sum(f.pos_quantity) as scanned
into #f
from sales f
join store ds on f.store_key = ds.store_key
join item di on f.item_key = di.item_key and di.country_id = 1
join #w w on f.calendar_key = w.calendar_key
join #s s on ds.customer_id = s.customer_id
join #i i on di.upc = i.upc
where (pos_quantity > 0 or pos_ext_price > 0)
group by s.holding, s.division, s.banner, s.subbanner, s.fulldescription, i.upc, i.description, i.item_size, i.item_uom, w.week_end_date
/*Pivot Table Output*/
declare @pivot varchar(max), @sql varchar(max)
set @pivot = ' ' select @pivot = @pivot + '[' + CONVERT(VARCHAR(10), week_end_date, 101) + '],'
from (select distinct week_end_date from #w) C order by week_end_date
set @pivot = left(@pivot, len(@pivot) - 1)
set @sql =
'select * from #f as A
pivot (sum(scanned) for week_end_date in ( ' + @pivot + ' ) ) as B
order by 1,2,3,4,5,6,7,10'
EXEC (@sql)
/*Active items */
select distinct i.upc,i.description,i.item
into #i
from dim_item i join dim_item_client ic on i.client_key = ic.client_key
where datediff(dd, i.start_date, '2/24/2007') >= 0
and datediff(dd, i.end_date, '9/2/2006') <= 0
and ic.client_key = 24391
and i.active = 1
and i.country_id = 1
create clustered index [ix_#i] on #i ([upc] asc) on [primary]
/* Active Locations */
select distinct s.customer_id, s.fulldescription, h.description as holding,
d.description as division, b.description as banner, sb.description as subbanner
into #s
from vw_store s
join subbanner sb on s.subbanner_key = sb.subbanner_key
join banner b on sb.banner_key = b.banner_key
join division d on b.division_key = d.division_key
join holding h on d.holding_key = h.holding_key
where datediff(dd, s.start_date, '2/24/2007') >= 0
and datediff(dd, s.end_date, '9/2/2006') <= 0
and s.active = 1
and s.country_id = 1
and b.channel_key = 80
create clustered index [ix_#s] on #s ([customer_id] asc) on [primary]
/*Week ending dates since September 2006 */
select distinct calendar_key, week_end_date
into #w
from calendar
where week_end_date between '9/2/2006' and '2/24/2007'
create clustered index [ix_#w] on #w ([calendar_key] asc) on [primary]
/*Scans for Active,items at Active, US, Locations*/
select s.holding, s.division, s.banner, s.subbanner, s.fulldescription, i.upc, i.description, i.item_size, i.item_uom, w.week_end_date,
sum(f.pos_quantity) as scanned
into #f
from sales f
join store ds on f.store_key = ds.store_key
join item di on f.item_key = di.item_key and di.country_id = 1
join #w w on f.calendar_key = w.calendar_key
join #s s on ds.customer_id = s.customer_id
join #i i on di.upc = i.upc
where (pos_quantity > 0 or pos_ext_price > 0)
group by s.holding, s.division, s.banner, s.subbanner, s.fulldescription, i.upc, i.description, i.item_size, i.item_uom, w.week_end_date
/*Pivot Table Output*/
declare @pivot varchar(max), @sql varchar(max)
set @pivot = ' ' select @pivot = @pivot + '[' + CONVERT(VARCHAR(10), week_end_date, 101) + '],'
from (select distinct week_end_date from #w) C order by week_end_date
set @pivot = left(@pivot, len(@pivot) - 1)
set @sql =
'select * from #f as A
pivot (sum(scanned) for week_end_date in ( ' + @pivot + ' ) ) as B
order by 1,2,3,4,5,6,7,10'
EXEC (@sql)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.