Generate single record with multiple values of stations - SQL 2000

• Kicking_Fish0713

  SSC Eights!

  Points: 896

  More actions

  October 22, 2008 at 3:54 am

  #383188

  Hi,

  Is there a way I could generate/populate single record with multiple values of Stations?

  Number Station Value Station1 Station2 Station3

  12345 1 10 NULL NULL NULL

  12345 2 20 NULL NULL NULL

  12345 3 30 NULL NULL NULL

  Expected result:

  Number Value Station1 Station2 Station3

  12345 10 10 20 30

  Thanks in advance!

• Cath Trimble

  SSCarpal Tunnel

  Points: 4201

  More actions

  October 22, 2008 at 7:20 am

  #888090

  Check out PIVOT in BOL. Your query would look something like this:

  SELECT Number

  , [1] AS Station1

  , [2] AS Station2

  , [3] AS Station3

  FROM

  (SELECT Number

  , Station

  , [Value]

  FROM Stations) s

  PIVOT

  (SUM([Value]) FOR Station IN ([1], [2], [3])) p

  Cath