To Pivot or not to Pivot

• Paul Anderson-304005

  SSC-Addicted

  Points: 451

  More actions

  June 29, 2009 at 5:02 am

  #139210

  Hi All,

  I want to be able to transform the following data

  [font="Courier New"]

  Name Month Year Value

  Rod 1 2009 10.5

  Jane 1 2009 11

  Freddy 1 2009 12

  Rod 2 2009 10

  Jane 2 2009 10

  Freddy 2 2009 13.1

  [/font]

  into

  [font="Courier New"]

  Name 1-2009 2-2009

  Rod 10.5 10

  Jane 11 10

  Freddy 12 13.1

  [/font]

  Can this be done with a pivot or not?

  Cheers,

  Andez

• PNS

  Right there with Babe

  Points: 773

  More actions

  June 29, 2009 at 5:37 am

  #1016733

  Hi,

  yes you can use pivot in the following way

  SELECT Name,[1] as [1-2009],[2] as [2-2009]

  FROM

  (

  SELECT NAME,Month,Year,Value

  FROM #t

  ) p

  PIVOT

  (

  MAX (Value)

  FOR [MONTH] IN

  ([1],[2])

  ) AS pvt

  Parul

• Paul Anderson-304005

  SSC-Addicted

  Points: 451

  More actions

  June 29, 2009 at 6:25 am

  #1016771

  Thanks Parul, that works fine.:-)