Hello, is there a way to concatenate column values from multiple rows using iDM GUI/DMC?

For example I have two columns with these rows.
MAKE MODEL
Nissan Xterra
Nissan Altima
Nissan Pathfinder
Honda Accord
Honda Pilot
Toyota Camry

I would like the output to be
MAKE MODEL
Nissan Xterra, Altima, Pathfinder
Honda Accord, Pilot
Toyota Camry

Thanks.

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

Sure. Create a source transformation like this:

MODELS | A256V | IF MAKE NE LAST MAKE THEN MODEL  ELSE MODELS || ( ' ' |  MODEL ) 

Then in your SQL object

 
MAKE   | Group By
MODELS | Max   

Also if the source data is in a recent release of ORACLE or DB2 you can use DB_EXPR with the native SQL function LISTAGG.

Thanks Clif.

I am using SQL Server 2012 for my data source. In regards to your comment about using DB_EXPR to invoke native SQL functions, is this expression available in source, SQL and target nodes in DMC?

Unfortunately there is no LISTAGG equivalent in MS SQL Server so DB_EXPR won't help in your case.

If you search for such a thing you'll find rather complex workarounds. They would be more suitable for use in a DBMS SQL Flow or a synonym created for an external SQL Script.

One more thing: for the technique I gave you works for your sample since all the rows with the same MAKE are grouped together.

The row concatenation is better implemented in DBMS than WebFOCUS/DataMigrator. You'll find different ways like this:

http://stackoverflow.com/quest...a-single-text-string

One thing you need to keep in mind is that you have to set a size limit to the returned string. I've done something similar in DB2, and it caused a "buffer overflow" error when the string size was more than it could handled.