All Forums Database
schepur 4 posts Joined 07/14
17 Jul 2014
Need Query/Procedure to concatenate multiple(n) rows into one row for a particular record dynamically

Hi,
I am facing a problem with concatinating multiple rows in order and storing in to a target table for particular records.
Pls help me. I need a procedure or any good thing. Below is my input sample data 

EMP_SOURCE

 

eid name   line_num   Addr

300 satya    1                *gandhi na+

300 satya    2                 *gar near hot+

300 satya    3                 *el baristha h+

300 satya    4                 *yderabad

301 raja      1                 *ramnthapur hy+

301 raja      2                 *derabad

302 shree    1                 *gachibowli ne+

302 shee     2                 *w street hyde+

302 shree    3                 *rabad

 

 

 
And Required Output is

EMP_TARGET

 

eid   name     Addr

300   satya      gandhi nagar near hotel baristha hyderabad

301   raja        ramnthapur hyderabad

302   shree      gachibowli new street hyderabad
 

 
*I have millions of records with distinct EID's in Source.
I tried Recursive function but it's using more spool and also its not concatinating in order(Data should be cancatinated with the respect of Line_Num). So Iam thinking that recursive is not a good thing.
Pls share me If you have any Idea.
 
Thanks,
Sathya.
 
 

dnoeth 4628 posts Joined 11/04
17 Jul 2014

Hi Sathya,
based on your example there's probably a known maximum line_num, so easiest to write is this, it's mainly cut&paste:

select eid, name, 
   max(case when line_num = 1 then trim(trailing '+' from substring(Addr from 2)) else '' end) ||
   max(case when line_num = 2 then trim(trailing '+' from substring(Addr from 2)) else '' end) ||
   max(case when line_num = 3 then trim(trailing '+' from substring(Addr from 2)) else '' end) ||
   max(case when line_num = 4 then trim(trailing '+' from substring(Addr from 2)) else '' end) ||
   max(case when line_num = 5 then trim(trailing '+' from substring(Addr from 2)) else '' end) ||
   max(case when line_num = 6 then trim(trailing '+' from substring(Addr from 2)) else '' end) ||
   ... repeat until the max line_num 
from t
group by 1,2

Recursion is also possible (and will concat in the right order if you do it correctly), but will probably use more spool and is only better when the max level is large or not known.

Dieter

schepur 4 posts Joined 07/14
17 Jul 2014

Thanks! Dnoeth,
I tried this one, If i have more than 100 records in ADDR column for a particular Eid and 2 records for another EID then also its executing all case statements for 2 records EID,it should execute only 2 case conditions with respect to Line_num. It is a performance issue in my case(*I have millions of records with distinct EID's in Source).
and also I noticed that if we use more than 4 case conditions  it's throwing error as
"Teradata Database] [TeraJDBC 13.00.00.06] [Error 3798] [SQLState 42000] A column or character expression is larger than the max size."
So I need Dynamic query which can execute loop with respect to the line_num.
 
 
Thanks,
Sathya.

schepur 4 posts Joined 07/14
17 Jul 2014

and also we dont  know exactly how many ADDR lines will come for particular EID,in future it might be greater than what we are giving the max number

You must sign in to leave a comment.