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;