SQL PIVOT Functions

  • I have a financial department requirement, where they require various data grouped by Year/Month.

    I have done this by using PIVOT - but the goal posts have now changed and they only want to see the previous 3 months along with lasts years data as a comparison.

    I have the following script ;

    ========================

    SELECT

    TrnYear,

    [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]

    FROM

    (

    SELECT

    ROW_NUMBER() OVER ( ORDER BY TrnYear DESC, right('00'+ rtrim(TrnMonth), 2) DESC ) as Row,

    TrnYear,

    (right('00'+ rtrim(TrnMonth), 2) ) as TrnMonth,

    Count( Invoice ) as InvoiceCount

    FROM ApInvoicePay

    Where

    TrnYear >= Year( GetDate()) -2

    Group by TrnYear, TrnMonth

    ) p

    PIVOT

    (

    Sum(InvoiceCount)

    FOR TrnMonth IN

    ( [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])

    ) AS Pvt

    where Row in ( 1,2,3,12,13,14,15 )

    ================================

    from the above I get the the following output ;

    ================================

    TrnYear010203040506070809101112

    2013NULLNULLNULLNULLNULLNULLNULLNULL652NULLNULLNULL

    2013NULLNULLNULLNULLNULLNULLNULL556NULLNULLNULLNULL

    2013NULLNULLNULLNULLNULLNULL679NULLNULLNULLNULLNULL

    2013NULLNULLNULLNULLNULL485NULLNULLNULLNULLNULLNULL

    2014NULLNULLNULLNULLNULLNULLNULL331NULLNULLNULLNULL

    2014NULLNULLNULLNULLNULLNULL488NULLNULLNULLNULLNULL

    2014NULLNULLNULLNULLNULL560NULLNULLNULLNULLNULLNULL

    ================================

    what I would like to see is the TrnYear only ONCE ..... am I missing one simple piece of grouping code ?

    Thanks

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • Hi,

    Since I don't have sample data to work with I took the AdventurWorks database as my foundation. I am not totally clear how your data looks like but below is what I was able to devise.. maybe it can help you with your code .

    select * from

    (

    select YEAR(ORderDATE) as Year_, MONTH(OrderDate) AS Month_pivot,SalesOrderID

    FROM Sales.SalesOrderHeader

    ) as t

    pivot

    (

    Count(salesOrderID) for month_pivot in ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])

    ) as p

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

Viewing 2 posts - 1 through 1 (of 1 total)

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