Most effective way to convert row into column

• Marc Abraham L. Vitalis

  Newbie

  Points: 1

  More actions

  May 22, 2005 at 11:59 pm

  #102542

  what is the most effective way for this in the query?

  here is the scenario:

  -table 'data' has a detail table 'datadetails'

  -'datadetails' has rows of items

  -'datadetails' has two columns 'name', 'amount'

  -if there is a detail item whose name is 'x' then the amount will be displayed in the x column other wise it will be 0

  -also, if there is a detail item whose name is 'y' then the amount will be displayed in the y column other wise it will be 0

  example

  DATA TABLE

  dataid data description

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

  1 sample data

  DATADETAILS TABLE

  dataid name amount

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

  1 x 1000.00

  there is no item y

  QUERY

  dataid x y total

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

  1 1000.00 0.00 1000.00

• DanKennedy

  Hall of Fame

  Points: 3736

  More actions

  May 24, 2005 at 1:48 am

  #560681

  This is a really simple example. Hope it helps.

  DECLARE @datadetails TABLE

  (

  dataid int,

  [name] char(1),

  amount money

  )

  INSERT INTO @datadetails VALUES (1, 'X', 34500)

  INSERT INTO @datadetails VALUES (2, 'X', 1600)

  INSERT INTO @datadetails VALUES (3, 'Y', 80)

  INSERT INTO @datadetails VALUES (4, 'X', 6890)

  INSERT INTO @datadetails VALUES (5, 'X', 1230)

  INSERT INTO @datadetails VALUES (6, 'Y', 84570)

  INSERT INTO @datadetails VALUES (7, 'X', 7480)

  INSERT INTO @datadetails VALUES (8, 'Y', 10)

  SELECT dataid,

  CASE [name]

  WHEN 'X' THEN amount

  ELSE 0

  END AS X,

  CASE [name]

  WHEN 'Y' THEN amount

  ELSE 0

  END AS Y,

  amount

  FROM @datadetails

  ---

  Dan
  www.firstcs.co.uk