How do I aggregate a string

[Anonymous]

I guess I am looking for a function with which I can flatten multiple rows to single row and multiple columns.

Input:

Name    Guarantor

Sally       Tom

Sally       Dick

Sally       Joe

Sue         Harry

Output:

Name      Guarantor1     Guarantor2     Guarantor3

Sally         Tom                 Dick                Joe

Sue          Harry

I need this in a straight table as pivot table is not an option.

See attached for my effort which gave limited results.

Thanks,

Dinesh.

[Clever_Anjos · Accepted solution]

Use the concat() function inside your load script grouping by "Name".
Example:

LOAD

Name,
CONCAT(Guarantor,'/') AS GuarantorConcatenated
RESIDENT <yourtable>

and inside your straight table you can use SUBFIELD to split again in columns

[#3]

Hi! Take a look at concat (search for it in the help(F1)).

Returns the aggregated string concatenation of all values of expression  iterated over a number of records as defined by a group  by clause. Each value may be separated by the string found in delimiter. The order of concatenation may be  determined by sort-weight. Sort-weight should return a numeric value  where the lowest value will render the item to be sorted first. If the word  distinct occurs before the expression, all duplicates will be  disregarded.

[#4]

How can I achieve this in the calculated dimension of a straight table ?

Thanks in advance!!