How to split column values into multiple rows based on a delimiter - Informatica PowerCenter

Question posted on Informatica Forum:

Can you please suggest me logic on how to make single row into multiple rows?

CURRENT OUTPUT

                    COLUMN-A     COLUMN-B      COLUMN-C

ROW1          1                         2                    MY||SUPPORT||INFORMATICA

 EXPECTED OUTPUT

                    COLUMN-A     COLUMN-B      COLUMN-C

ROW1          1                         2                    MY

ROW2          1                         2                    SUPPORT

ROW3          1                         2                    INFORMATICA

The number of rows to be generated depending on COLUMN-C. 

in this example in column-c there are 3 concatenated values so number of rows to generated are 3 and column-a and column-b should be unique for 3 rows...

like if there are 4 concatenated values in column-c then number of rows to be generated are 4.

Solution:

select CASE  WHEN  a.l = 1

              THEN  substr(col, 1, instr(col,',',1,a.l)-1)

               ELSE  substr(col, instr(col,',',1,a.l-1)+1,

                              CASE WHEN instr(col,',',1,a.l)-instr(col,',',1,a.l-1)-1 > 0

                                   THEN instr(col,',',1,a.l)-instr(col,',',1,a.l-1)-1

                                   ELSE length(col)

           END

           ) END EXPECTED_OUTPUT

          from

(select replace('MY||SUPPORT||INFORMATICA','||',',') col from dual)t,

(SELECT level l FROM DUAL CONNECT BY LEVEL <=

(SELECT Max(length(replace('MY||SUPPORT||INFORMATICA','||',',')) - length(replace('MY||SUPPORT||INFORMATICA','||',''))+1) FROM dual )) a

Oracle Database as source: Use above SQL query

Flat File Source: Use above SQL query in SQL Transformation and pass the column value as input.

Note:- Query is just for your reference, and you may have to change the query as per your requirement.