columns to rows

• randyt

  SSC Veteran

  Points: 202

  More actions

  August 9, 2002 at 3:40 pm

  #158659

  Ok here goes,

  I have a table with this basic structure

  vehicle_id int

  chain varchar(10)

  spacing varchar(10)

  teeth varchar(10)

  what i need to do is the opposite of a pivot

  table. I want the data to look like this

  vehicle_id property value

  1 chain 43

  1 spacing .43

  1 teeth 32

  the only way I can make it work is by using a cursor, but a more set oriented approach would be better

  thanks for any help

  cheers

  Randy

• NPeeters

  SSChampion

  Points: 12536

  More actions

  August 12, 2002 at 1:10 am

  #434415

  You could use :

  select v.vehicle_id, u.property, u.value

  from vehicles v,

  (select vehicle_id, 'chain', chain from vehicles

  UNION

  select vehicle_id, 'spacing', spacing from vehicles

  UNION

  select vehicle_id, 'teeth', teeth from vehicles

  ) u

  where v.vehicle_id = u.vehicle_id