June 25, 2008 at 8:05 am
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?
June 28, 2008 at 4:32 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply