Data Migrator Assistance needed for Pivoting a source with unknown upper limit number of records

Looking for any help available to be able to pivot a source table which has varrying number of records that need to be concatenated into a single record.

Source:
ISN SEQ Expl
1 1 A
1 2 B
1 3 C
2 1 D
2 2 E

Target (should look like)
ISN EXPL_OUT
1 ABC
2 DE

There could be more that 100 source records that need to be concatenated into a single record.
Thanks

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Meena,

I see that a case has been opened for this topic. Please keep us posted on the final solution from the case. Thank you in advance for sharing with all.

Regards,

Kathryn

I see from the hottrack case that the source is a DB2 table so you can take advantage of the DB2 function LISTAGG using DB_EXPR. Create your select statement so it reads:

SELECT
  ISN, 
  DB_EXPR (LISTAGG (EXPL, ' ') WITHIN GROUP (ORDER BY SEQ)) AS EXPL_OUT  
  FROM source
  GROUP BY ISN

Thanks Clif for the recommendation. It looks like LISTAGG is available for DB2 9.7+, since we are currently on 9.1 this doesn't work for us. However we are moving to Oracle soon, and hopefully we will be able to use this function then. Loogks like this funtion is supported by Oracle 11g.

Indeed LISTAGG is in DB2 9.7. Also ORACLE 11g Release 2. I'm glad that will work for you.

MySQL has a similar function GROUP_CONCAT(). Other databases though not so much.

DB_EXPR by the way requires DM 7.7.05. For earlier releases you can use a DBMS SQL Flow.

we have now moved to Oracle 11g, and i'm trying to use DB_EXPR(Listagg....). The field that I am aggregating is char 60 (A60V), and I am writing it to a TX50 field (oracle CLOB). When I use DB_EXPR(Listagg...), by default, the output format is A4000V.

when I execute this flow, I get an error stating...
SQL PASSTHRU - DATATYPE NOT SUPPORTED FOR EDA
Return Code = 1513.

I was able to get the flow to run, if i used CAST on the DB_EXPR expression, and changed the format to A2000V.

Limitation of 4000 char is concerning, but 2000 characters really isn't feasible for us. Is there another option we could use.

Thanks
Meena

Meena - This may point you in the right direction. https://techsupport.informatio...om/sps/81572017.html

I haven't worked with CLOBs in a while. I take it that that the 1513 error number is an Oracle error number. Please confirm.

Rhonda