November 7, 2008 at 11:34 am
When using the PIVOT operation, if the column has no values then a NULL is in that column. Is there any way to have that column be some default value?
You can see what I'm talking about with the following sql. I want all of the WeekXAmount columns to have a zero if there is a null.
I've tried making a temp table (@) with default values, but the NULLs carry on through.
Thanks,
Wayne
declare @FridayDate datetime
set @FridayDate = '20081107'
declare @FridayDates table (RowID integer identity, WeekStart datetime, WeekEnd datetime)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate, @FridayDate+6)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+7, @FridayDate+13)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+14, @FridayDate+20)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+21, @FridayDate+27)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+28, @FridayDate+34)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+35, @FridayDate+41)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+42, @FridayDate+48)
insert into @FridayDates (WeekStart, WeekEnd) values (@FridayDate+49, @FridayDate+55)
DECLARE @Projections TABLE (
budget_category varchar(3),
amount numeric(10,2),
DueDate datetime,
Interval char(1),
Period char(2))
insert into @Projections
select 110,139.50,'2008-11-21 00:00:00.000','D','21' UNION
select 110,139.50,'2008-12-19 00:00:00.000','D','21' UNION
select 111,27.50,'2008-11-07 00:00:00.000','D','07' UNION
select 111,27.50,'2008-12-05 00:00:00.000','D','07' UNION
select 182,489.81,'2008-11-14 00:00:00.000','D','15' UNION
select 182,489.81,'2008-12-12 00:00:00.000','D','15' UNION
select 200,75.00,'2008-11-21 00:00:00.000','D','27' UNION
select 220,167.40,'2008-11-14 00:00:00.000','D','17' UNION
select 220,167.40,'2008-12-12 00:00:00.000','D','17' UNION
select 411,782.85,'2008-11-07 00:00:00.000','D','08' UNION
select 411,782.85,'2008-12-05 00:00:00.000','D','08'
select * from @Projections
select
pvt.budget_category,
pvt.[1] [Week1Amount],
pvt.[2] [Week2Amount],
pvt.[3] [Week3Amount],
pvt.[4] [Week4Amount],
pvt.[5] [Week5Amount],
pvt.[6] [Week6Amount],
pvt.[7] [Week7Amount],
pvt.[8] [Week8Amount]
from
(select
p.budget_category,
p.Amount,
fd.RowID
from @Projections p
INNER JOIN @FridayDates fd ON p.DueDate = fd.WeekStart) AS p1
PIVOT
(
sum([Amount])
for [RowID] IN ([1],[2],[3],[4],[5],[6],[7],[8])
) AS pvt
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 7, 2008 at 11:40 am
Well - you can do it a few ways. The easiest is probably to simply use the isnull function on the columns AFTER the pivot operation (i.e. in the top-most query):
...
select
pvt.budget_category,
isnull(pvt.[1],0) [Week1Amount],
isnull(pvt.[2],0) [Week2Amount],
isnull(pvt.[3],0) [Week3Amount],
isnull(pvt.[4],0) [Week4Amount],
isnull(pvt.[5],0) [Week5Amount],
isnull(pvt.[6],0) [Week6Amount],
isnull(pvt.[7],0) [Week7Amount],
isnull(pvt.[8],0) [Week8Amount]
from
....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2008 at 1:09 pm
Thanks Matt.
(I hate the answers that are SOOO obvious... why didn't I think of it???:))
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 7, 2008 at 1:25 pm
WayneS (11/7/2008)
Thanks Matt.(I hate the answers that are SOOO obvious... why didn't I think of it???:))
Hey - it's a Friday....no biggie!!!!
Speaking of which - time to replace the caffeine drip..:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply