query to bring in multiple row values into one record

Database
Enthusiast

query to bring in multiple row values into one record

My data looks something like this:

table1:

ID | Line | Code

1 | 1 | a

1 | 2 | b

2 | 1 | d

2 | 2 | e

2 | 3 | f

table2:

Code | Name

a | alpha

b | beta

d | delta

e | epsilon

f | phi

A common join brings the following results:

ID | Line | Name

1 | 1 | alpha

1 | 2 | beta

2 | 1 | delta

2 | 2 | epsilon

2 | 3 | phi

The output I'd really be interested in accomplishing is:

ID | Name_Concatenate

1 | alpha, beta

2 | delta, epsilon, phi

I've seen a number of other, similar responses, https://forums.teradata.com/forum/database/concatenate-value-of-multiple-rows-into-one-single-row-1 , for example. However, I can't seem to translate to my data model. If I could receive some help with the above, super simple example, I would be able to convert to my needs. Please note that I'm dealing with 100k+ records in both "table1" and "table2". I'm only looking to concatenate the "name" field while still retaining other information, from other tables, on the ID meta level, not line/detail level.

There is no limit on how many "lines" there are per ID. Average is any where from 3-5, but can go upwards to 25, so a pivot or multiple joins winds up taking up spool space, and I'm looking for efficiency.

The previous post I'm referencing uses the following code:

 

CREATE VOLATILE TABLE vt_temp AS (
SELECT
Parent_ID
,Child_ID
,city_nm
,EMAIL_ADDR
,ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY child_id) AS rn
FROM temp
) WITH DATA PRIMARY INDEX(parent_id) ON COMMIT PRESERVE ROWS;

WITH RECURSIVE rec_test(parent,child, location,mail,LVL)
AS
(
SELECT parent_id,child_id (VARCHAR(1000)),city_nm (VARCHAR(1000)),email_addr, 1
FROM vt_temp
WHERE rn = 1
UNION ALL
SELECT parent_id, TRIM(child_id) || ', ' || child, TRIM(city_nm) || ', ' || location ,email_addr,LVL+1
FROM vt_temp INNER JOIN rec_test
ON parent_id = parent
AND vt_temp.rn = rec_test.lvl+1
)
SELECT parent,child, location,mail,LVL
FROM rec_test
QUALIFY RANK() OVER(PARTITION BY parent ORDER BY LVL DESC) = 1;

 

Can you help me translate? 

create volatile table code_names (
select table1.ID, table1.Line, table2.name
from table1 left outer join table2 on table1.code = table2.code )
with data primary index (ID) on commit preserve rows;

Please correct me if I'm wrong here, but the working example only uses one table and I do need to join tables to receive mnemonics. As far as the recursive block goes, I'm at a complete loss. Any help would be greatly appreciated. 

Thank you

7 REPLIES
Enthusiast

Re: query to bring in multiple row values into one record

This is my actual code:

create volatile table eob_test as (
select ap.claim_id, cl.mnemonic , ap.line
from ap_claim_eob_code ap
left outer join clarity_eob_code cl on ap.eob_code_id = cl.eob_code_id
) with data primary index (claim_id) on commit preserve rows;

with recursive rec_test(claim, name, line_num)
as
(
select claim_id, mnemonic, line
from eob_test
where line = 1
union all
select claim_id, TRIM(mnemonic) || ', ' || name, line_num+1
from eob_test
inner join rec_test on claim_id = claim and eob_test.line = rec_test.line_num+1
)

select claim, name
from rec_test
QUALIFY RANK() OVER(PARTITION BY claim ORDER BY line_num asc) = 1;

It results with the value for line 1, but does not concatenate any additional values

Junior Contributor

Re: query to bring in multiple row values into one record

You forgot to increase the size of the "mnemonic" column, thus it's the original size and the added lines are silently truncated:

select claim_id, mnemonic (VARCHAR(1000)), line

But what's your Teradata release? Are XML services available?

SELECT * FROM dbc.FunctionsV
WHERE FunctionName = 'XMLAGG';

select ap.claim_id,
RTRIM(XMLAGG(TRIM(cl.mnemonic) || ', '
ORDER BY ap.line
) (VARCHAR(10000)),', ')
from ap_claim_eob_code ap
left outer join clarity_eob_code cl on ap.eob_code_id = cl.eob_code_id

Enthusiast

Re: query to bring in multiple row values into one record

Hi Dieter, thanks for the reply. I'm using teradata to query against an Epic Clarity database and do not have the permissions to determine versioning. However, when I ran the select statement you shared, I receive the following error:

"SELECT Failed. 3706: Syntax error: expected something between '(' and the 'TRIM' keyword"

After playing around, it doesn't look like I can run the XMLAGG function.

Code now is as follows: 

create volatile table eob_test as (
select ap.claim_id, cl.mnemonic, ap.line
from ap_claim_eob_code ap
left outer join clarity_eob_code cl on ap.eob_code_id = cl.eob_code_id
) with data primary index (claim_id) on commit preserve rows;

with recursive rec_test(claim, name, line_num)
as
(
select claim_id, mnemonic (VARCHAR(1000)), 1
from eob_test
where line = 1
union all
select claim_id, TRIM(mnemonic) || ', ' || name, line_num +1
from eob_test
inner join rec_test on claim_id = claim and eob_test.line = rec_test.line_num+1
)

select claim, name
from rec_test
QUALIFY RANK() OVER(PARTITION BY claim ORDER BY line_num asc) = 1;

Still showing multiple rows per claim_id, no concatenation with commas. But it does run, so I'm wondering if there's an issue with my recursive logic call

Enthusiast

Re: query to bring in multiple row values into one record

This was my issue:

QUALIFY RANK() OVER(PARTITION BY claim ORDER BY line_num asc) = 1;

I had asc instead of desc, now I'm concatenating properly, but there are still some duplicate claim id returns, but I'm learning that there are some duplicate line numbers. this is more of a context issue for how the product is used, which I can approach easily enough.

Thank you for your time, its greatly appreciated!

Enthusiast

Re: query to bring in multiple row values into one record

One last question,

My source data has duplicate "line" numbers. There is a column which has a flag in it, so we can see something like the following:

ID | Line | Name | Flag

1 | 1 | alpha | Y

1 | 1 | beta | N

1 | 2 | delta | N

1 | 3 | epsilon | Y

The query as is will return two rows

ID | name

1 | epsilon, delta, alpha

1 | epsilon, delta, beta

I see a few possible approaches:

Instead of using line number, use the row() function and just pull everything into the temp table, regardless of flag. This would work in most cases, but it may be beneficial to have the values split into two fields, one with the flag set to true, the other false.

1 | epislon, delta, alpha, beta

If I wanted to have two fields, such as this:

ID | Flag_Y | Flag_N

1 | Epsilon, Alpha | Delta, Beta

What would you suggest?

I'm thinking I could pull in the flag and use case statement fields in the recursive call to build the two fields. Thoughts on this?

Junior Contributor

Re: query to bring in multiple row values into one record

To split in two columns you don't need two Recursive Queries, simply split the data into tw columns when you create the Volatile Table.

Additionally you can change the logic to start with the highest line number instead of 1, thus you don't have to QUALIFY RANK:

create volatile table eob_test as (
select claim_id,
line,
max(case when flag = 'Y' then trim(mnemonic) end) as mnemonic_Y,
max(case when flag = 'N' then trim(mnemonic) end) as mnemonic_N,
case when line = max(line) over (partition by claim_id) then 'Y' end as StartHere -- start with the highest number
from tab
group by 1,2
) with data primary index (claim_id) on commit preserve rows;

with recursive rec_test(claim, name, name2, line_num)
as
(
select claim_id,
coalesce(trim(mnemonic_Y), '') (VARCHAR(1000)),
coalesce(trim(mnemonic_N), '') (VARCHAR(1000)),
line
from eob_test
where StartHere = 'Y'
union all
select claim_id,
coalesce(trim(mnemonic_Y) || ', ', '') || name,
coalesce(trim(mnemonic_N) || ', ', '') || name2,
line_num - 1
from eob_test
inner join rec_test on claim_id = claim and eob_test.line = rec_test.line_num - 1 -- counting down
)
select claim, name, name2
from rec_test
where line_num = 1 -- final row
Enthusiast

Re: query to bring in multiple row values into one record

Thank you! The next time I get some dev time, I will be playing with this. Its greatly appreciated.