All Forums Database
SAP 73 posts Joined 08/14
22 Sep 2014
Rows should be appended to a column in teradata

Hi ,
My scenario :-
 
name            Desc
sibhi            good
sibhi             bad
 
My result should be as below
 
name         desc 
sibhi         good bad
 

SAP
Raja_KT 1246 posts Joined 07/09
22 Sep 2014

select name1,regexp_replace(tdstats.udfconcat(trim(desc1)),'"','',1,0,'i') from your_table group by 1;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
22 Sep 2014

Thanks a lot raja !!! but i dont have access to execute function tdstats.udfconcat :( :(  Please help me somehow !!!!
I hitting my head for past 2 hours !!!

SAP

Raja_KT 1246 posts Joined 07/09
22 Sep 2014

Which version of TD you are in? Can you try something like this?

select name1,max(case when rn=1 then desc1 else '' end)

||max(case when rn=2 then desc1 else '' end) from 

(select name1,desc1,row_number()over(partition by name1 order by name1) rn from your_table) a

group by 1

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
22 Sep 2014

but i need a generic one which should accomodate even if n number of values and concatenate it into same column.. like below 
Name  Desc
Sibhi    good bad n n n n n n n n n n n n ......

SAP

Raja_KT 1246 posts Joined 07/09
22 Sep 2014

With recursive may take a lot of spool. You can think of udf for concatenation, or you can extend your list above :).
you have not told me the version , you are using.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
22 Sep 2014

Sorry Raja ... I am using TD 14 using Teradata Studio Express 15 . 
UDF is totally new to me . I looked into Info.teradata.com but i need more help in preparing a UDF for my scenario . Can u pls do any sort of help ?
 

SAP

24 Sep 2014

Hi,
I hope the below query serve's ur purpose:
SEL name,DESCRIP r FROM(select a.name name,
(CASE WHEN SUBSTR(a.desc1,0,length(a.desc1)+1)<>SUBSTR(b.desc1,0,length(b.desc1)+1) THEN trim(a.desc1)||trim(b.desc1) END) DESCRIP,RANK(DESCRIP) r
FROM YOUR_TABLE a,YOUR_TABLE b
where DESCRIP is not null)x
where r=1;
 
Thanks
Jugal

You must sign in to leave a comment.