How to combine columns from different rows

• ramya-251841

  Grasshopper

  Points: 11

  More actions

  June 25, 2008 at 8:05 am

  #189610

  I have a tableA

  Col1 Col2 Col3

  123 abc apple

  123 def orange

  123 ghi grapes

  Another tableB

  Col1

  123

  I need to come up with a select to display

  123 abc ** def ** ghi apple**orange**grapes...

  Right now I'm using a function that loops thru Table1 and returns abc ** def ** ghi for 123.

  The sql is

  Select colA, function(123) as colb, function(123) as colc

  The above works but since it is using functions, it takes very long time while executing millions of rows from TableB

  I made these a view by making

  select colA, function(colA)

  Still it takes longer b/c I have maximum of 8 views.

  What is the best method to do this?

• Ben Lloyd-417375

  SSC Journeyman

  Points: 80

  More actions

  June 28, 2008 at 4:32 am

  #835738

  You might be able to get away with a self-join on TableA, e.g.:

  SELECT

  a.Col1 AS colA,

  a.Col2 + ' ** '

  + IsNull( b.Col2, '' ) + ' ** '

  + IsNull( c.Col2, '' ) AS colB,

  a.Col3 + '**'

  + IsNull( b.Col3, '' ) + '**'

  + IsNull( c.Col3, '' ) AS colC

  FROM

  TableA a

  INNER JOIN TableA b

  ON a.Col1 = b.Col1

  AND a.Col2 < b.Col2

  INNER JOIN TableA c

  ON a.Col1 = c.Col1

  AND b.Col2 < c.Col2

  You'd have to play around with it a bit to get it to handle cases where there were any more or less than 3 colB values for each colA, if that was required.