Query help - Row Join (concatenate)

[MuseVic]

Hi,

I have a brain block and need to solve the following.
I am stuck on thoughts like concatenate bu the joins are rows not columns.

2 table query, with out put via Crystal report to look like

01/01/01 The Record one flew over the tree

when the tables look like :

Table 1

ID ID2 Data
12 abc one flew over
13 abc the tree,

Table 2
ID Date Data
abc 01/01/01 The record


Thoughst Please

 

[vongrunt]

You want to concatenate Data for rows with each ID2 value?

If true, is there a possibility that 3 or more rows have the same ID2?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[MuseVic]

Vongrunt..

true, ID2 is the link that relates each row of data .

True there could be unlimted rows of ABC.

to clarify table has "chunk" column which is a count of idicator of the number of rows of data

ID ID2 Chunk Data
12 abc 0 one flew over
13 abc 1 the tree,
14 abc 2 and the tree fell down


My intention is to extract up to x ( say 3) rows for the report and then trail the report with "... see blah blah for more information" That way if there are > 10 rows the report is not looking like a novel.

Hope this adds some clarity.

Thanks

 

[gmmastros]

In my opinion, your best approach would be to create a User Defined Function (UDF) that concatenates the row data for you. There was a thread recently that shows how to create a UDF that returns a comma delimited string. Start with that approach (obviously you would want to use a space instead of a comma).

Here's the thread I was referring to. thread183-1204377

After looking at this, and attempting to implement this, if you have any further questions, then let us know.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom