Can you concatenate child field values in a single SQL statement

[LlomaxX]

This is more of a SQL question than VB, but most of us are well-versed in both, so I thought I would try here first.

Our VB app is tied to a mainframe DB2 database, and the data modelers have of course normalized this to within an inch of it's life. What I would like to do is create a single SQL that will select rows from a table, and as a field in that SELECT statement, include a concatenation of all of the values of a single column in all of the child rows associated with the current row.

So, if I have the following:

Field A Field B Field C
Field X Field Y Field Z
A B C
1 2 3
4 5 6
D E F
7 8 9
10 11 12
13 14 15

....I want to get:

Field A Field B Field C Field D
A B C 1+4
D E F 7+10+13

How can I do this with a single SQL Statement?

 

[aco2x]

I'm not sure I totally understand your data structure but I'm assuming the child data(Table2 in my example) is in another table with a foreign key (FieldA) to the parent(Table1). If so, try a sub query in your join like this:

SELECT Table1.FieldA, FieldB, FieldC, FieldXSum
FROM Table1 INNER JOIN (SELECT FieldA,  Sum(FieldX) As FieldXSum  FROM Table2 GROUP BY FieldA) Table2  ON Table1.FieldA = Table2.FieldA;
[\Code]
Using this query with Table1 =
[code]
FieldA  FieldB  FieldC
A       B       C
X       Y       Z
[\code]

and Table2 = 
[code]
FieldA  FieldX  FieldY  FieldZ
A       1       2       3
A       4       5       6
X       7       8       9
X       10      11      12
X       13      14      15
[\code]

My results are:

[Code]
FieldA  FieldB  FieldC  FieldXSum
A       B       C       5
X       Y       Z       30
[\Code]

 

[LlomaxX]

I guess I shouldn't have used numbers in my example...this is what I want:

Table1 =

FieldA  FieldB  FieldC
A       B       C
X       Y       Z

and Table2 =

FieldA  FieldX  FieldY  FieldZ
A       L       M       N
A       O       P       Q
X       R       S       T
X       U       V       W
X       I       J       K

My results should be:

FieldA  FieldB  FieldC  FieldD
A       B       C       L@O
X       Y       Z       R@U@I

Where @ is just a delimiting character of some kind.

Is this possible using a single SQL statement? I can't think of how to do it without some sort of looping program logic to create the fourth, new field. I would like to get it returned from the SQL statement as part of my cursor.

Is there any sort of SQL function (coalesce, pivot, or some of those other things I don't know what they mean) that could show me data like this?