Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display 0 instade of ISNULL in a PIVOT table Expand / Collapse
Author
Message
Posted Monday, September 12, 2011 3:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 13, 2011 11:40 AM
Points: 12, 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
Post #1173247
Posted Monday, September 12, 2011 4:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:20 AM
Points: 411, Visits: 1,400
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?
Post #1173267
Posted Monday, September 12, 2011 6:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 1,237, Visits: 9,799
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.
Post #1173356
Posted Monday, September 12, 2011 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 13, 2011 11:40 AM
Points: 12, 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]...
Post #1173370
Posted Monday, September 12, 2011 6:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 1,237, Visits: 9,799
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
Post #1173371
Posted Monday, September 12, 2011 7:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:20 AM
Points: 411, Visits: 1,400
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?
Post #1173416
Posted Tuesday, September 13, 2011 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 13, 2011 11:40 AM
Points: 12, 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

Post #1174243
Posted Tuesday, September 13, 2011 8:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 1,237, Visits: 9,799
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.
Post #1174251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse