Converting multiple row values into single columns

[grajmca_sgp123]

Hi All,

I have a requirement here where i need to convert multiple row values into single columns with some special characters

ID   Status

1      AB

1      CDEF

1       GHI

1       JKLMN

2       XYZ

2      UVWX

2       PR

ID Status

1   AB // CDEF// GHI // JKLMN

2   XYZ// UVWX // PR

if we use Concat function it will concatenate all the field values . But my requirement is i need '//' also between each field value.

do we have any other solutions to resolve this issue.

Thanks

[tresesco · Accepted solution]

Use delimeter '//' with concat(), like:

Concat( Distinct Status, '//')

[Anonymous]

May be combination of Group by and Concat() like this?

Load

ID,

Concat(Status, '//')

Resident TableName

Group by ID;

[maxgro]

Source:

load *, rowno() as rn inline [

ID   Status

1      AB

1      CDEF

1       GHI

1       JKLMN

2       XYZ

2      UVWX

2       PR

] (delimiter is spaces) ;

Load

ID,

Concat(Status, ' // ', rn) as Status

Resident Source

Group by ID;

DROP Table Source;