June 9, 2015 at 9:40 am
Hello,
I am using AdventureWorks2008R2 to do some practice on Pivot, here is my query:
Select * From
(SELECT year([TransactionDate]) as yr, left(datename(month,[TransactionDate]),3)as [month], isnull(sum([Quantity]), 0) as Amount
FROM [AdventureWorks2008R2].[Production].[TransactionHistory]
group by year([TransactionDate]), left(datename(month,[TransactionDate]),3)
)s
Pivot
(SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)) AS piv
The result:
yrjanfebmaraprmayjunjulaugsepoctnovdec
2007NULLNULLNULLNULLNULLNULLNULLNULL446021276122271849355188
200827672532730136127831978442781742587221415124765727444NULLNULLNULL
Why isnull(sum([Quantity]), 0) doesn't return 0 for those month with NULL? How to fix the script?
Thanks.
June 9, 2015 at 9:53 am
Change the SELECT * to specify all the columns needed and generated after the pivot. Change the ISNULL to those columns.
June 9, 2015 at 9:58 am
Your derived table query isn't returning any rows for those periods so there is no row to apply ISNULL to. You need to provide data for those months with a cross join to a calendar table or some other way to generate data for months that have not data.
Looks like this thread, http://www.sqlservercentral.com/Forums/Topic519288-338-1.aspx, has a good answer by GSquared. Here's the idea he uses:
WITH p AS (
SELECT
*
FROM
(
SELECT
YEAR([TransactionDate]) AS yr,
LEFT(DATENAME(MONTH, [TransactionDate]), 3) AS [month],
ISNULL(SUM([Quantity]), 0) AS Amount
FROM
[AdventureWorks2008R2].[Production].[TransactionHistory]
GROUP BY
YEAR([TransactionDate]),
LEFT(DATENAME(MONTH, [TransactionDate]), 3)
) s PIVOT
( SUM(Amount) FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) ) AS piv
)
SELECT
p.yr,
ISNULL(jan, 0) AS jan,
ISNULL(feb, 0) AS feb,
ISNULL(mar, 0) AS mar,
ISNULL(apr, 0) AS apr,
ISNULL(may, 0) AS may,
ISNULL(jun, 0) AS jun,
ISNULL(jul, 0) AS jul,
ISNULL(aug, 0) AS aug,
ISNULL(sep, 0) AS sep,
ISNULL(oct, 0) AS oct,
ISNULL(nov, 0) AS nov,
ISNULL(dec, 0) AS dec
FROM
p;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 9, 2015 at 11:16 am
Thanks to every reply
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply