Split one column into multiple rows in sql in Teradata

We recently had a discussion on our Facebook Fan Page regarding how to split one column values into multiple rows. We got many responses like using for loop in stored procedures etc. We can split values in one column into multiple rows by using Recursive queries. Let's see an example below:
First we create a table which will hold multiple emails as one value in a column.

CREATE VOLATILE TABLE VT_MANY_TO_ONE
 ,NO FALLBACK,NO LOG, NO JOURNAL
 (
 SEQ_NO SMALLINT,
 EMAIL_LIST VARCHAR(100)
 )PRIMARY INDEX(SEQ_NO)
 ON
 COMMIT PRESERVE ROWS;

Let's insert a row into this table with multiple emails separated by '~'.

INSERT INTO VT_MANY_TO_ONE
VALUES(3,'ABC@ABC.COM~DEF@DEF.COM~XYZ@XYZ.COM');

Now we will write a recursive query to split this column value into multiple rows.

 WITH RECURSIVE SPLIT_ONE_TO_MANY (POS,SEQ, NEW_STRING, REAL_STRING) AS
 (
 SELECT
 0, 0, CAST('' AS VARCHAR(100)),TRIM(EMAIL_LIST)
 FROM VT_MANY_TO_ONE
 UNION ALL
 SELECT
 CASE WHEN POSITION('~' IN REAL_STRING) > 0
 THEN POSITION('~' IN REAL_STRING)
 ELSE CHARACTER_LENGTH(REAL_STRING)
 END DPOS,
 SEQ + 1,
 TRIM(BOTH '~' FROM SUBSTR(REAL_STRING, 0, DPOS+1)),
 TRIM(SUBSTR(REAL_STRING, DPOS+1))
 FROM SPLIT_ONE_TO_MANY
 WHERE DPOS > 0
 )
 SELECT SEQ,TRIM(NEW_STRING)
 FROM SPLIT_ONE_TO_MANY
 WHERE SEQ > 0;

Leave a Reply

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