SQL to turn multivalue fields into multiple rows

[balu536]

Hi all,
In one of my jobs i have a logic to be achieved which is explained below

I/p

COLA COLB COLC COLD COLE COLF
1 A1 B1 C3 D5 E7
2 A11 C12 F4 G34 H54;A12;C13
3 V1 X3 G2 S12 A12;E2;D67

i.e there might be multiple values present in the data which are seperated by special character ' ; '.I need to split these multivalues into seperate records i.e the required o/p is

O/p

COLA COLB COLC COLD COLE COLF
1 A1 B1 C3 D5 E7
2 A11 C12 F4 G34 H54
2 A11 C12 F4 G34 A12
2 A11 C12 F4 G34 C13
3 V1 X3 G2 S12 A12
3 V1 X3 G2 S12 E2
3 V1 X3 G2 S12 D67

Please help me in achieving this logic.

Regards,
Balakrishna

*Note: Title edited to be more descriptive - Content Editor*

[Sainath.Srinivasan]

If the number of occurance is small, you can write in seperate output links or split into columns and pivot them.

[Plagvreugd]

Seems like I just answered a very similar post. viewtopic.php?t=128756

By the way, please give your questions a subject that's a bit more descriptive.

[balu536]

I have achieved this logic using Query in Oracle stage,
Below are the details

SELECT
DISTINCT KEY,
COLA,
COLB,
LEVEL AS LEV ,
TRIM(
SUBSTR(txt,INSTR(txt,';',1,level)+1,INSTR(txt, ';', 1, level+1) - INSTR (txt, ';', 1, level) -1)) AS COLB_MODIFIED
FROM ( SELECT KEY,TRIM(COLA) AS COLA,TRIM(COLB) AS COLB,';'||TRIM(COLB)||';' AS txt FROM DEVT3_ECCNS.CNX033E_MULTIVALUE_TEST ) CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,';',''))-1
ORDER BY KEY,LEV

I/p:

KEY COLA COLB
1 RAJA 10;230;34
2 RAVI 56;38;21
3 RAM 5

O/p:
KEY COLA COLB
1 RAJA 10
1 RAJA 230
1 RAJA 34
2 RAVI 56
2 RAVI 38
2 RAVI 21
3 RAM 5

[keshav0307]

so the problem solved, mark the topic resolved

Note:
you will get answer as per the question.
you didn't put your requirement correctly in first place?
you only ask about pivoting logic in datastage.