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*
SQL to turn multivalue fields into multiple rows
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 9
- Joined: Tue Sep 20, 2005 2:10 am
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.![Wink ;)](./images/smilies/icon_wink.gif)
By the way, please give your questions a subject that's a bit more descriptive.
![Wink ;)](./images/smilies/icon_wink.gif)
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
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
-
- Premium Member
- Posts: 783
- Joined: Mon Jan 16, 2006 10:17 pm
- Location: Sydney, Australia