Teradata Recursive CTE Concatenate Rows

Question

Hello all I have the following CTE...what I'm trying to do is have an Id and then a concatenated list of Status

so for example...if I have

1, 1234, Hot
2, 1234, Cold
3, 1234, Warm

I want to end up with

1234,'Hot,Cold,Warm'

My CTE

with recursive temp_table(RowNumber, Id, Status) as (  
select 
    row_number() over (order by OBJECT_STATUS ) as RowNumber  
    ,OBJECT_ID  
    ,cast(OBJECT_STATUS as varchar(100))  as Status  
from  
    CORE_ORDER_STATUS   
where  
    OBJECT_ID = 'OR000008387722'  
union all  
select   
     a.RowNumber + 1 as NextOne  
     ,b.OBJECT_ID  
    ,a.Status || ',' || cast(b.OBJECT_STATUS as varchar(5))  as Status  
from  
    CORE_ORDER_STATUS b  
inner join  
    temp_table a  
on  
    a.Id = b.OBJECT_ID  
where  
    a.RowNumber = b.NextOne  
)  
select * from temp_table;

Thanks Andrew, contrary to popular belief...English is my first language. I fixed the question — jim, Dec 15 '14 at 17:41

score 1 · Answer 1 · answered Dec 15 '14 at 16:44

1

Your SELECT from the CTE is returning every row. You want the record with the highest RowNumber which I believe will require a HAVING RowNumber=MAX(RowNumber) included along with a GROUP BY clause for the columns you are returning.

answered Dec 15 '14 at 16:44

Rob Paller

7,736
29
26

score 1 · Accepted Answer · answered Dec 15 '14 at 17:05

Assuming that Rob Paller's interpretation of your problem is correct (and I think it is), here's how I'd handle this, with some kludgy volatile temp tables for source data:

create volatile table vt_test as (
select
 '1234' as object_id,
cast ('Hot'  as varchar(1000)) as object_status

) with data
primary index (object_id)
on commit preserve rows;

insert into vt_test values ('1234','Cold');
insert into vt_test values ( '1234','Warm');
insert into vt_test values ( '5678','Red');
insert into vt_test values ( '5678','Blue');
insert into vt_test values ( '5678','Green');


create volatile table vt_two as(
select
row_number() over (partition by object_id order by object_status ) as RN,
object_id,
object_status
from
vt_test) with data
primary index (object_id)
on commit preserve rows;   


with  RECURSIVE CTE (RN,Object_id,Object_Status,counter) as (
SELECT
RN,
object_id,
Object_Status,
cast(1 as integer)
from
vt_two
where RN = 1
UNION ALL
select
b.rn,
b.object_id,
a.object_status || ',' || b.object_status as Object_Status,
counter + 1
from
vt_two a
inner join cte b
on a.object_id = b.object_id
and a.rn = b.counter + 1
) 

select
*
from
CTE
qualify rank() over (partition by object_id order by counter desc) = 1;

Which will give you:

1   1234    Warm,Hot,Cold   3
1   5678    Red,Green,Blue  3

The qualify clause at the end is the key here. Comment it out, and it's pretty clear what it does.

You have to be very careful with recursive CTEs, at least in Teradata. It's very easy to write a query that will basically never end.

You can add a qualification in your CTE to place a limit on the depth of the recursion. Typically on your `counter` column. — Rob Paller, Dec 19 '14 at 14:00

Teradata Recursive CTE Concatenate Rows

2 Answers2