Post reply

Using Pivot operator

  • June 10, 2008 at 3:56 am

    #208183

    Dear All,

    I am genereting crosstab reports by using the pivot operator,in that report null values comes how can i eliminate null values.

    for example while using the below queries

    select name,lid,[20S] as '20s',[40S] as '40s'

    from

    (

    Select name,lid,equipgrp,cnt

    from #tmptrad ) ps

    PIVOT

    (

    sum(cnt) for

    equipgrp in ([20S],[40S])

    )AS pvt

    i got the result as

    ------------------------

    NAME lid 20s 40s

    Silver p1 16 NULL

    silver2 p2 NULL 14

    silver3 p2 NULL 2

    i want to make NULL as 0(zero)

    Thanks and Regards,

    N.Prabhakaran

  • June 10, 2008 at 4:06 am

    #826509

    TRy this...

    select name,lid,ISNULL([20S],0) as '20s',ISNULL([40S],0) as '40s'

    from

    (

    Select

    name

    ,lid

    ,equipgrp

    ,ISNULL(cnt ,0) as cnt

    from #tmptrad

    ) ps

    PIVOT

    (

    sum(cnt) for equipgrp in ([20S],[40S])

    )AS pvt

    I change the values of the result set with the ISNULL function

    thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • June 10, 2008 at 7:28 am

    #826628

    Dear Chris,

    It's working fine.

    Thanks a lot.

    Regards,

    N.Prabhakaran

  • June 11, 2008 at 5:32 am

    #827163

    The Sql Server Pivot is a no-brainer in the pejorative sense. What were they thinking? 🙂 If you want something that doesn't insult your intelligence and isn't simply the sauce without the beef, check out RAC 😀

    www.rac4sql.net

    www.beyondsql.blogspot.com

Viewing 4 posts - 1 through 3 (of 3 total)

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