need some help rows to column

• torpkev

  SSCarpal Tunnel

  Points: 4819

  More actions

  May 14, 2009 at 7:27 am

  #209309

  I was wondering if someone could help me with some sql.. i was thinking of using a pivot, but I'm not sure.. I'm trying to use to replace a large and very inefficient sql statement.

  I have a table called table1 with data like:

  field_id, tname, oid, lno, data_value <- fields
  ============================ <- data below this line
  tot_sh_unit, om, oid123, 11
  wgt_uom, om, oid123, 04
  total_qty, om, oid123, 322

  obviously i have a LOT more data.. but only these 3 field_id's

  what I'm trying to get to is something like:

  tname, oid, tot_sh_unit, wgt_uom, total_qty <- fields
  ================================== <- data below this line
  om, oid123, 11, 04, 322

  So basically take the data_value from each row and make it a column named by the field_id

  Does that make sense?

  I'd appreciate any help anyone can give me.. I can provide table creation and some insert statements if it'd help.

  Thanks!

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

  I long for a job where my databases dont have any pesky users accessing them 🙂

• FelixG

  SSCrazy

  Points: 2550

  More actions

  May 14, 2009 at 7:48 am

  #994522

  SELECT tname, oid, [tot_sh_unit], [wgt_uom], [total_qty]

  FROM

  (SELECT field_id,tname, oid, data_value

  FROM #test) p

  PIVOT

  (

  SUM (data_value)

  FOR field_id IN

  ( [tot_sh_unit], [wgt_uom], [total_qty])

  ) AS pvt

• torpkev

  SSCarpal Tunnel

  Points: 4819

  More actions

  May 14, 2009 at 7:55 am

  #994534

  Thanks a lot Felix, thats exactly what I was looking for..my pivot attempt just wasn't quite right.. but that works perfectly

  Thanks again!

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

  I long for a job where my databases dont have any pesky users accessing them 🙂