IF Column1 = Column2 => Merge data of Column3 in one cell

Teradata Applications
N/A

IF Column1 = Column2 => Merge data of Column3 in one cell

I have this table:






Book Task Assignee
723983 A5Q Maria
723983 A5Q Ken
723983 A5Q Phil
723983 WC13 Brian
724025 FRMDS Jacob
724025 FRMDS Rick
723956 TMI820 Sandra
723956 TMI820 Simon
723956 W1A1 Ray
723956 GD23 Fabricio
723956 GD23 Mario

And -  I want to achieve this:





Book Task Assignee
723983 A5Q Maria; Ken; Phil
723983 WC13 Brian
724025 FRMDS Jacob; Rick
723956 TMI820 Sandra; Simon
723956 W1A1 Ray
723956 GD23 Fabricio; Mario

So, if more than 1 assignee is assigneed to the same task, in the same book -> merge them into ONE cell (;).

Is there a way to do it in Teradata SQL Assistent..? 

Regards,

Theena

3 REPLIES

Re: IF Column1 = Column2 => Merge data of Column3 in one cell

Hi ,

Any body found the Query for the above requirement??? Please share.

Thanks,

Gati

Enthusiast

Re: IF Column1 = Column2 => Merge data of Column3 in one cell

you should be able to accomplish this with some group by statements, left outer joins and a couple of <> conditions

Enthusiast

Re: IF Column1 = Column2 => Merge data of Column3 in one cell

Based on Ulrich's response for http://forums.teradata.com/forum/database/parent-child-relationship-flattening

Please find below query:


Create volatile table temp 

(Book integer,

Task Varchar(20),

Assignee Varchar(20)) on commit preserve rows;

insert into temp (723983,'A5Q',' Maria');

insert into temp (723983,'A5Q',' Ken');

insert into temp (723983,'A5Q',' Phil');

insert into temp (723983,'WC13','Brian');

insert into temp (724025,'FRMDS','Jacob');

insert into temp (724025,'FRMDS','Rick');

insert into temp (723956,'TMI820','Sandra');

insert into temp (723956,'TMI820','Simon');

insert into temp (723956,'W1A1','Ray');

insert into temp (723956,'GD23','Fabricio');

insert into temp (723956,'GD23','Mario');

sel * from temp;

  create volatile table temp1

  as(

  select  book,

   task,

   assignee,

  row_number()over(partition by book,task order by assignee) rnk

  from temp) with data on commit preserve rows;

 

 

  sel * from temp1;

 

 WITH RECURSIVE RECTBL(book, task, Assignee,rnk) AS

(

  select  book,

   task,

   assignee,

   rnk

  from temp1

  where rnk = 1

 

  union all

 

  select t.book,

                t.task,

                t.assignee||','||r.assignee,

                t.rnk

    from temp1 t,rectbl r

   where t.book = r.book

   and t.task = r.task

   and t.rnk = r.rnk +1   

    )sel book,task, assignee from rectbl 

    qualify row_number() over (partition by book,task order by rnk desc) = 1

Result set :

book task Assignee

1 723956 GD23 Mario,Fabricio

2 723956 TMI820 Simon,Sandra

3 723956 W1A1 Ray

4 723983 A5Q Phil, Maria, Ken

5 723983 WC13 Brian

6 724025 FRMDS Rick,Jacob


Please provide DDL and insert statements from next time onwards..

- GK