Rows should be appended to a column in teradata

Database
SAP
Enthusiast

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

7 REPLIES
Enthusiast

Re: Rows should be appended to a column in teradata

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

SAP
Enthusiast

Re: Rows should be appended to a column in teradata

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 !!!

Enthusiast

Re: Rows should be appended to a column in teradata

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

SAP
Enthusiast

Re: Rows should be appended to a column in teradata

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 ......

Enthusiast

Re: Rows should be appended to a column in teradata

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.

SAP
Enthusiast

Re: Rows should be appended to a column in teradata

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 ?

Enthusiast

Re: Rows should be appended to a column in teradata

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