Solved

Replace Null Cells in Pivot Table

Posted on 2007-03-19
1
499 Views
Last Modified: 2008-03-06
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.item_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)
0
Comment
Question by:kcmoore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18748557
use
coalesce( sum(yourcolumnname),0) as yourcolumnname
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question