Concatenating multiple data into single row


Concatenating multiple data into single row


Re: Concatenating multiple data into single row

Hi all ,

I need some inputs for the following issue.

I am getting data as below this if I run a SQL

Dept No    Name    Salary     Region     Designation         Stream

1                A          1000       AO              Engineer               IT

1                    B              2000         AO                Engineer                IT

2                     C             3000         JO                    Sr. Engineer        IT

1                   D                 4000        FO                 Manager               IT

1                   E                 500           AO                Engineer               ?

1                  F                    400           AO                 Engineer              ?

But the required  output should be like this

Dept No    Name         Salary              Region        Designation           Stream 

1                   A,B         1000,2000            AO                  Engineer               IT

2                     C               3000                   JO                 Sr. Engineer          IT

1                   D                 4000                   FO                 Manager               IT

1                   E                 500                      AO                Engineer               ?

1                  F                    400                      AO               Engineer              ?

Conditions applied were :

- If Stream =IT  and data in all the columns(except Name , Salary) are same   then concatenate the  data in to single row with a comma seperator.

- If stream is not IT then display the data as it is.

To acheive the above I used the following TD function

cast(regexp_replace(tdstats.udfconcat(trim(A.NAME)),'"','',1,0,'i') as varchar(100)) as Name,

cast(regexp_replace(tdstats.udfconcat(trim(b.Salary)),'"','',1,0,'i') as varchar(100)) as Salary

After using this I am facing an error :

9134 : Results exceeds the output argument  size limit

Any inputs / suggestions  are appreciated