Display 0 instade of ISNULL in a PIVOT table

Question

Display 0 instade of ISNULL in a PIVOT table

mustapha-721538

Right there with Babe

Points: 774
More actions
September 12, 2011 at 3:28 am

#247779

Hello,
The result of the bellow query is diasplayed at the end of the code.
I want to display 0 and even use the SUM function to get an addition of all columns at the very end:
The query:
SELECT MaingroupNo, MainGroupName, ArticleNo, Name,
[1], [2], [3], [4], [5], [6], [7], [8], ([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]) as total
FROM
(SELECT Aydin.SalesFigures.MaingroupNo, Aydin.MainGroup.Name as MainGroupName, Aydin.SalesFigures.ArticleNo, Aydin.Article.Name, isnull(Period, 0.00) AS Period , TotalSalesQuantity
FROM Aydin.SalesFigures INNER JOIN
Aydin.Article ON Aydin.SalesFigures.ArticleNo = Aydin.Article.ArticleNo INNER JOIN
Aydin.MainGroup ON Aydin.SalesFigures.MainGroupNo = Aydin.MainGroup.MainGroupNo
where YEAR ='2011' and Period <9 and Aydin.Article.StockProfileNo = 1 ) AS SourceTable
PIVOT
(
sum(TotalSalesQuantity)
FOR Period IN ( [1], [2], [3], [4], [5], [6], [7], [8])
) AS PivotTable
order by MaingroupNo;
The result:
GroupNoMainGroupNameNoName12345678total
110Kolonial (12%)4051Citron juice 0,750 ml13192219209714123
110Kolonial (12%)4314Citronjuice 330ml Minella2NULLNULLNULLNULLNULL14NULL
best regards
MM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

Please post some ddl (table definitions) plus some example data. From just your query it is not clear from which tables columns like 'Period' and 'YEAR' are taken. Knowing this is key to a fast solution for your problem. Please see the links in my footer for information on how to post ddl and example data.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

HowardW One Orange Chip Points: 29109 More actions · Answer 2

You just need to wrap ISNULL's around your final SELECT - if there are no rows that match the PIVOT criteria, it'll resolve to NULL:

SELECT MaingroupNo, MainGroupName, ArticleNo, Name,

ISNULL([1],0) AS [1], ISNULL([2],0) AS [2] ...

Also, be careful with your Total column. You need to wrap each one in an ISNULL to prevent one NULL value making the whole calculation NULL.

mustapha-721538 Right there with Babe Points: 774 More actions · Answer 3

Thank you for this solution.

One issue is solved now I´m displaying 0 instead of NULL in all the columns except the total column it still displays NULL when at least one column is NULL/0 from the set of columns [1][2][3]...

HowardW One Orange Chip Points: 29109 More actions · Answer 4

mustapha-721538 (9/12/2011)
Thank you for this solution.
One issue is solved now I´m displaying 0 instead of NULL in all the columns except the total column it still displays NULL when at least one column is NULL/0 from the set of columns [1][2][3]...

As per my post above:

Also, be careful with your Total column. You need to wrap each one in an ISNULL to prevent one NULL value making the whole calculation NULL.

I did add it 30 seconds or so after the orginal post, so maybe you didn't see it. Anything plus NULL always equals NULL

R.P.Rozema SSChampion Points: 12317 More actions · Answer 5

You need to get rid of the pivot and make it into a crosstab. Have a look at the crosstabs post in my footer. Jeff Moden describes in the linked to article a much more efficient & elegant way to do what you want to do.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

mustapha-721538 Right there with Babe Points: 774 More actions · Answer 6

Thanks a lot its working perfect, but is there any way to prevent the MainGroup.Name from output more then one time.

SELECT Aydin.MainGroup.Name, Aydin.SalesFigures.ArticleNo, Aydin.Article.Name, Aydin.Article.QuantityPerUnitTextSale,

STR(round(SUM(CASE WHEN PERIOD = 1 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [Januari],

STR(round(SUM(CASE WHEN PERIOD = 2 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [Februari],

STR(round(SUM(CASE WHEN PERIOD = 3 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [Mars],

STR(round(SUM(CASE WHEN PERIOD = 4 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [April],

STR(round(SUM(CASE WHEN PERIOD = 5 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [Maj],

STR(round(SUM(CASE WHEN PERIOD = 6 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [Juni],

STR(round(SUM(CASE WHEN PERIOD = 7 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [Juli],

STR(round(SUM(CASE WHEN PERIOD = 8 THEN TotalSalesQuantity ELSE 0 END), 0),15,0) AS [Augusti],

STR(round(SUM(TotalSalesQuantity),0),15,0) AS TOTAL

FROM Aydin.SalesFigures INNER JOIN

Aydin.Article ON Aydin.SalesFigures.ArticleNo = Aydin.Article.ArticleNo INNER JOIN

Aydin.MainGroup ON Aydin.SalesFigures.MainGroupNo = Aydin.MainGroup.MainGroupNo

where YEAR = year(getdate()) and Aydin.SalesFigures.Period <9 and Aydin.Article.StockProfileNo = 1

GROUP BY Aydin.MainGroup.Name, Aydin.SalesFigures.Year, Aydin.SalesFigures.ArticleNo, Aydin.Article.Name, Aydin.Article.QuantityPerUnitTextSale

order by Aydin.MainGroup.Name, Aydin.Article.Name

HowardW One Orange Chip Points: 29109 More actions · Answer 7

It'll display once for every distinct group of your group by clause as with any other GROUP BY...

In the row that you believe is duplicated, one or more of the other fields will be different.