Teradata Recursive Query Syntax with Example

teradata

Recently Krish asked us a query in which data in one row should be concatenated with data in other row. And this should continue for all subsequent rows. It looks like kind-of cumulative sum kind of operation for character columns. So we thought of doing it using rescursive queries:
Let us see the example below:

CREATE MULTISET VOLATILE TABLE vt_source
(
dept_no INTEGER,
emp_name VARCHAR(100)
)ON COMMIT PRESERVE ROWS;

INSERT INTO vt_source SEL 10,’A’;
INSERT INTO vt_source SEL 10,’B’;
INSERT INTO vt_source SEL 10,’C’;
INSERT INTO vt_source SEL 10,’D’;
INSERT INTO vt_source SEL 20,’P’;
INSERT INTO vt_source SEL 20,’Q’;
INSERT INTO vt_source SEL 20,’R’;
INSERT INTO vt_source SEL 20,’S’;

This is the Source table with the sample data. Now we thought of creating another table to order rows in the same order we want it to concatenate. This can be different in different situation. For this example we are keeping it as dept_no and emp_name. (This is what is required as per screenshot). We could have used derived tables in place of volatile table however recusrive queries comes with limitation and we cannot use derived tables in it.

CREATE VOLATILE TABLE vt_source_1 AS
(
SEL dept_no,emp_name,
ROW_NUMBER() OVER(ORDER BY dept_no ASC,emp_name ASC) AS row_id
FROM vt_source
)
WITH DATA
ON COMMIT PRESERVE ROWS;

Now we will start our recursive query. First part will select the first row and then will keep on adding subsequent rows to the values.

WITH RECURSIVE str_concat(dept_no,emp_name ,row_id)
AS
(
SEL dept_no,emp_name ,row_id
FROM vt_source_1 tb1
WHERE tb1.row_id=1
UNION ALL
SEL tb1.dept_no,tb2.emp_name||’,’||tb1.emp_name ,tb1.row_id
FROM vt_source_1 tb1,str_concat tb2
WHERE tb2.row_id+1=tb1.row_id
)
SEL dept_no, emp_name AS emp_list FROM
(SEL dept_no,emp_name,row_id FROM str_concat)tb3
ORDER BY 1,2

Using this simple query we can achieve desired output.
Hope this helps !!!

4 thoughts on “Teradata Recursive Query Syntax with Example”

  1. This is wonderful. I’ve been scouring the web, and even Teradata documentation for simple examples on how to use Recursive Querying to do something like this…it is luck that I came across your post.

    Perhaps an update, I was able to use a derived table (subquery) in my Recursive query so I didnt need to use Volatile tables, perhaps that was a limitation with older Teradata DB versions.

    1. Another update, with Teradata version 14, you are able to use multiple WITH clauses; more specifically you can embed a non-recursive WITH clause within your recursive query, and use it as a means to derive information to use anywhere within your recursive query.

Leave a Reply

Your email address will not be published. Required fields are marked *