Display 0 instade of ISNULL in a PIVOT table

  • 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

  • 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?

  • 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.

  • 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]...

  • 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

  • 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?

  • 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

  • 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.

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

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