Need Query/Procedure to concatenate multiple(n) rows into one row for a particular record dynamically

Database

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.

3 REPLIES
N/A

Re: Need Query/Procedure to concatenate multiple(n) rows into one row for a particular record dynamically

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.

Re: Need Query/Procedure to concatenate multiple(n) rows into one row for a particular record dynamically

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.

Re: Need Query/Procedure to concatenate multiple(n) rows into one row for a particular record dynamically

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