Split delimited column into separate rows

UDA
Enthusiast

Split delimited column into separate rows

Hello,
I have a record that has a column that contains delimited data, how can I split the delimited data into separate rows?

Current look
1 | A, B, C
2 | A, C

What I want:
1 | A
1 | B
1 | C
2 | A
2 | C

Thanks,
Paul
2 REPLIES

Re: Split delimited column into separate rows

Hey paulcy, any luck on this?

Teradata Employee

Re: Split delimited column into separate rows

This will work. 

I setup the test like this.

CREATE TABLE LOAD.Test_x

(

Delimited_String VARCHAR(100)

)

INSERT INTO LOAD.Test_x

VALUES ('1 | A, B, C')

VALUES ('2 | A, B, C,D')

Rather than writing this from scratch I found an excellent blog which I adapted a bit of recursive sql I found there.

http://walkingoncoals.blogspot.com/2009/12/fun-with-recursive-sql-part-2.html

Here is the result

WITH RECURSIVE parse_list (response_key, delim_pos, item_num, element, remainder) AS

(

        SELECT

        SUBSTRING( Delimited_String FROM 1 FOR POSITION('|' IN Delimited_String))

        ,0, 0, CAST('' AS VARCHAR(100))

        ,SUBSTRING(Delimited_String FROM POSITION('|' IN Delimited_String)+2 FOR Char_Length(Delimited_String))

        FROM

        LOAD.Test_x

UNION ALL

    SELECT response_key,

           CASE WHEN POSITION(',' IN remainder) > 0

             THEN POSITION(',' IN remainder)

             ELSE CHARACTER_LENGTH(remainder) END dpos,

           item_num + 1,

           TRIM(BOTH ',' FROM SUBSTR(remainder, 0, dpos+1)),

           TRIM(SUBSTR(remainder, dpos+1))

    FROM   parse_list

    WHERE  dpos > 0

)

SELECT response_key, element

FROM   parse_list p

WHERE  item_num > 0

ORDER BY response_key, item_num;

Here is the result:

    response_key    element

1    1 |    A

2    1 |    B

3    1 |    C

4    2 |    A

5    2 |    B

6    2 |    C

7    2 |    D

Hope this helps.

-Fred