Group Aggregated Data better in SQL or in CODE (in terms of performance)

Question

I would like to ask for opinion on making aggregate data by concatenating strings. If I have a column aggregate but I want to concatenate then in an aggregate column, which is faster in terms of performance? Doing one SQL then just aggregating then in the CODE. Or selecting the main data then querying one at a time.

For Example:

TABLE_A        
COL_A_1         COL_A_2
A               a
B               b
C               c

TABLE_B
COL_B_1         COL_B_2
a               Text1
a               Text2
a               Text3
b               Text4
b               Text5

The table is joined at COL_A_2 = COL_B_1, the aggregate column is COL_B_2.

Option 1 (Doing one SQL)

SELECT TABLE_1.COL_A_1, TABLE_1.COL_A_2, TABLE_2.COL_B_2 
FROM TABLE_A 
LEFT OUTER JOIN TABLE_B ON TABLE_A.COL_A_2 = TABLE_2.COL_B_1
ORDER BY TABLE_1.COL_A_1

Then in the code, just loop for the result set and aggregate the COL_B_1. (ex using Java)

String oldColA1 = "";
InfoEntity currInfo = null;
for (InfoEntity info : infoList) {
  if (!oldColA1.equals(info.colA1)) {
    currInfo = info;
  }

  if (currInfo.colB2 == null || currInfo.colB2.equals("")) {
    currInfo.colB2 = info.colB2;
  } else {
    currInfo.colB2 += info.colB2;
  }
  oldColA1 = info.colA1;
}

Option 2 (Doing several SQL)

SELECT TABLE_1.COL_A_1, TABLE_1.COL_A_2
FROM TABLE_A 
ORDER BY TABLE_1.COL_A_1

Then select for each in code (Ex Java)

for (InfoEntity info : infoList) {
  // Select TableB entity based on info.colA2
  ...
  tableBList = dao.selectTableB(info.colA2);
  ...
  for (TableBEntity b : tableBList) {
    info.colB2 += b.colB2;
  }
}

I usually do option 1, because I think making multiple SQL select maybe costly. But I am not sure about this. But is there another way on doing this kind of aggregation. I already search the net and in standard SQL there is no string concatenation aggregation. Also, the illustration on TableA is over simplified, usually TableA is a complex query of multiple table joined together and so is TableB.

Answer 1

I usually do option 1, because I think making multiple SQL select is costly.

You're correct - trips to the database should be as few as possible.

Group concatenation is possible though - MySQL has GROUP_CONCAT, since 4.1. With SQL Server, you can use:

SELECT @out = COALESCE(@out + ',' + t.column, t.column)
  FROM TABLE t

Check this link for an example that should work in Oracle 9i+.