SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display 0 instade of ISNULL in a PIVOT table


Display 0 instade of ISNULL in a PIVOT table

Author
Message
mustapha-721538
mustapha-721538
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 61
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:
GroupNo MainGroupName No Name 1 2 3 4 5 6 7 8 total
110 Kolonial (12%) 4051 Citron juice 0,750 ml 13 19 22 19 20 9 7 14 123
110 Kolonial (12%) 4314 Citronjuice 330ml Minella 2 NULL NULL NULL NULL NULL 1 4 NULL


best regards
MM
R.P.Rozema
R.P.Rozema
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 1717
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
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
HowardW
HowardW
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6535 Visits: 9892
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
mustapha-721538
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 61
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
HowardW
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6535 Visits: 9892
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
R.P.Rozema
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 1717
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
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
mustapha-721538
mustapha-721538
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 61
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
HowardW
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6535 Visits: 9892
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search