Concatenation of a string

[#1]

I would like to build a string as the concatenation of a series of strings from various rows of a table.

Example: I have a table like this:

Id      ProgId      String

1      0                A0

1      1                A1

1      2                A2

1      3                A3

2      0                B0

3      0                C0

3      1                C1

3      2                C2

4      0                D0

4      1                D1

I would like to get a table like

Id           TotalString

1           A0*A1*A2*A3

2           B0

3           C0*C1*C2

4           D0*D1

could someone tell me how to do?

I think you have to use a loop cycle type

EACH FOR

...

but I don't know well

Somebody can tell me some interesting links for this argoment?

               Thank All Very Much

[MK_QSL]

Use below in your script

======================

Temp:

Load * Inline

[

Id,      ProgId,      String

1,      0,                A0

1,      1,                A1

1,      2,               A2

1,      3,                A3

2,      0,                B0

3,      0,                C0

3,      1,               C1

3,      2,                C2

4,      0,                D0

4,      1,                D1

];

Join

Load

  Id,

  Concat(String,'*') as TotalString

Resident Temp

Group By Id;

================

Now you can check your requirement by creating a table box for

Id and TotalString

[its_anandrjs]

For string use

Concat(String,'*' ) as TotalString

[its_anandrjs]

Write like

tmp:

Load * inline

[

Id,      ProgId,      String

1,      0,                A0

1,      1,                A1

1,      2,                A2

1,      3,                A3

2,      0,                B0

3,      0,                C0

3,      1,                C1

3,      2 ,               C2

4,      0,                D0

4,      1,                D1

];

Final:

Load

Id,

Concat(String,'*') as TotalString

resident tmp

group by Id;

drop table tmp;

and plot field Id, TotalString

[rustyfishbones]

Use

Concat(String,'*') as MyNewString

and GROUP BY Id

[rustyfishbones]

See the attached

Regards

Alan

[#7]

Thank Alan very much.

But I would like to do the concatenation maintaining the order of the ProgId column.

Then get the strings:

* A1 * A2 * A3 A0

B0

C0 C1 * C2 *

D1 * D0

See attached File.

  Thank

[#8]

Hey,

I may have found the solution.It is enough to do:

FinalTable:

Load Id,

     CONCAT(String,'*',ProgId)  as TotalString

RESIDENT Table

GROUP BY Id;

   Thank All

[maxgro]

source:

load * inline [

Id  ,    ProgId   ,   String

1 ,     0 ,               A0

1 ,     1 ,               A1

1 ,     2 ,               A2

1 ,     3 ,               A3

2 ,     0 ,               B0

3 ,     0 ,               C0

3 ,     1 ,               C1

3 ,     2 ,               C2

4 ,     0 ,               D0

4 ,     1 ,               D1

];

table1:

NoConcatenate load

Id,

ProgId,

if(Peek(Id)=Id,  peek(NewString) & '*' & String, String) as NewString

Resident

source

order by Id, ProgId;

drop Table source;

table:

NoConcatenate load

Id, NewString

Resident table1

where Peek(Id) <> Id

order by Id, ProgId desc;

DROP Table table1;

[#10]

PFA,