combine multiple row/column results into one row

• Jeff-132889

  Ten Centuries

  Points: 1112

  More actions

  April 19, 2005 at 4:13 pm

  #68973

  I did a bad job of explaining what I'm trying to do yesterday so I'll give it another try today.  I have the following table:

  id     key     val

  1       x        1

  1       y        1

  2       x        0

  2       y        1

  3       x        0

  3       y        0

   

  I need to present the result set as:

  id     key     val     key2   val2

  1       x        1        y        1

  2       x        0        y        1

  3       x        0        y        0

   

  Thanks in advance for advice........

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  April 19, 2005 at 4:25 pm

  #553607

  If the table always contains two rows for each id, then this should work.

  SELECT t1.id, t1.key, t1.val, t2.key as key2, t2.val as val2

  FROM sometable t1

  INNER JOIN sometable t2 ON t1.id = t2.id

  WHERE t1.key = 'x'

  AND t2.key = 'y'

  But I guess it is not that simple. Maybe this will help you get started anyway.

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/