Group By and Concatenation

[sallymay]

Hello,

I would like to know if there is a way to group by certain fields and also concatenate the contents of one field based on that group by. Here is a sample dataset:

RECORD #1--------------------
FIELD VALUE
ID 123
Name ABC
Description Comment1

RECORD #2--------------------
FIELD VALUE
ID 123
Name ABC
Description Comment2

RECORD #3--------------------
FIELD VALUE
ID 987
Name XYZ
Description Comment3

I would like to take these records group by the FIELD, ID, and NAME AND concatenate the Description field so that the data in the target table would be represented as:

RECORD #1--------------------
FIELD VALUE
ID 123
Name ABC
Description Comment1 Comment2

RECORD #2--------------------
FIELD VALUE
ID 987
Name XYZ
Description Comment3

I'm not sure how to concatenate the Description field as I need to do a group by that doesn't include the Description field.

Any help would be greatly appreciated.

Thanks,
Sally

 

[blom0344]

Sally, it can be done, though it is pretty awkward. (it is a sort of reverse normalization, followed by concatenation of ports.
First transformation will be a RANKER assigning a sequence to the rows, that resets with the ID value:

ID NAME DESCRIPTION RANK
123 ABC comment1 1
123 ABC comment2 2
987 XYZ comment3 1

Next transformation is an expression that holds as many outgoing ports as the maximum value of RANK in the dataset:

expression in outgoing port:

=IIF(RANK=1,DESCRIPTION,NULL) port1
=IIF(RANK=2,DESCRIPTION,NULL) port2
etc.

Third step is aggregating everything on one line for each ID
Use aggregator with group by on ID,NAME.
Use Min or Max on incoming ports port1,port2, etc

Fourth step is concatenating all the ports:

=port1||port2||...............||portlast


Ties Blom
Information analyst
tbl@shimano-eu.com