SQL PIVOT Functions

• SteveEClarke

  SSCertifiable

  Points: 5161

  More actions

  August 18, 2014 at 4:13 am

  #151369

  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.

• MMartin1

  One Orange Chip

  Points: 27826

  More actions

  August 19, 2014 at 5:57 pm

  #1739439

  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

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