All Forums Teradata Applications
Theena 1 post Joined 08/12
30 Aug 2012
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

 

 

gatidivineboy 1 post Joined 08/12
31 Aug 2012

Hi ,

 

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

 

Thanks,

Gati

VandeBergB 182 posts Joined 09/06
31 Aug 2012

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

Some drink from the fountain of knowledge, others just gargle.

Inang 5 posts Joined 08/12
31 Aug 2012

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

You must sign in to leave a comment.