All Forums Extensibility
sri899 5 posts Joined 09/15
02 Sep 2015
Concatenating multiple data into a single row
sri899 5 posts Joined 09/15
02 Sep 2015

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

You must sign in to leave a comment.