0

I have the following result set:

ID  |  mark   |  cost   | comment  
 1      yel       45        array
 3      yel       45        tack

Now I want to only have 1 row like:

ID  |  mark   |  cost   | comment  
 1      yel       45        array tack

if mark and cost are the same.

Any ideas on how to do that?

Taryn
  • 242,637
  • 56
  • 362
  • 405
Thevagabond
  • 323
  • 2
  • 9
  • 34
  • 1
    Please look at this: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – hogni89 Aug 20 '13 at 08:59
  • 1
    possible duplicate of [Create a delimitted string from a query in DB2](http://stackoverflow.com/questions/3728010/create-a-delimitted-string-from-a-query-in-db2) – GarethD Aug 20 '13 at 09:05
  • Starting from version 9.7.4 DB2 supports `LISTAGG()` string aggregate function. You may [look](http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0058709.html) at it. – Nick Krasnov Aug 20 '13 at 09:16

1 Answers1

1
select min(id),
       mark,
       cost,
       substr( xmlserialize( xmlagg( xmltext( concat( ' ', comment ) ) ) as varchar( 1024 ) ), 3 )
  from t1
 group by mark,
          cost;
Romesh
  • 2,291
  • 3
  • 24
  • 47