ETL SQL

Learn Extract Transform Load using SQL & PySpark

Recursive queries in 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.

Leave a Reply

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