Data Experts Help Needed Please Help Me

[massmaharaj]

ippudu data ila undi column a is primary key

column a column b
1 E903|0004
2 E001|E004|005|006
3 3395|8484|39843


SOURCE IS DATA IS LIKE ABOVE

IS THERE ANY POSSIBLITY TO GET LIKE BELOW
column a column b
1 E903
1 0004
2 E001
2 E004
2 005
2 006
3 3395
3 8484
3 39843

CAN WE DO IN INFORMATICA OR ORACLE

I USE INFORMATICA OR ORACLE

[mettastar]

do you know max how many pipes will be there in Second field?

[Silver_mani]

datastage lo 2 min lo chesta idi ....

[cherlapalli_jailer]

[quote name='Silver_mani' timestamp='1374180841' post='1303974285']
datastage lo 2 min lo chesta idi ....
[/quote]
chesi or hint ikkada ivvu will be useful for others too

@TS
PL/SQL SP lo cheyyavachu

[Silver_mani]

[quote name='cherlapalli_jailer' timestamp='1374181036' post='1303974329']

chesi or hint ikkada ivvu will be useful for others too

@TS
PL/SQL SP lo cheyyavachu
[/quote]

In Datastage


You can Seperate the Pipe Delimited data in Column b in to temporary columns either by using COlumn_Import stage of by using Field function in transformer . Then for every Key column map it to the Seperated field

[cherlapalli_jailer]

[quote name='Silver_mani' timestamp='1374181436' post='1303974398']
In Datastage


You can Seperate the Pipe Delimited data in Column b in to temporary columns either by using COlumn_Import stage of by using Field function in transformer . Then for every Key column map it to the Seperated field
[/quote]
col A val same vundali for those data it seems

[color=#282828][font=helvetica, arial, sans-serif]1 E903|0004[/font][/color]

should be

[color=#282828][font=helvetica, arial, sans-serif]1 E903[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]1 0004[/font][/color]

[massmaharaj]

ennaina undachu

[massmaharaj]

plsql ela raatyachu hints please

[massmaharaj]

/* Formatted on 7/18/2013 4:45:42 PM (QP5 v5.163.1008.3004) */
SELECT id, REGEXP_SUBSTR (list, '[^,]+', 1, rn) split
FROM (SELECT id, list FROM r1)
CROSS JOIN
( SELECT ROWNUM rn
FROM (SELECT MAX (LENGTH (REGEXP_REPLACE (list, '[^,]+'))) + 1 ax_value
FROM (SELECT id, list FROM r1))
CONNECT BY LEVEL <= max_value)
WHERE REGEXP_SUBSTR (list,
'[^,]+',
1,
rn)
IS NOT NULL
ORDER BY id


i found this in google

[url="http://manikandaprabhum.blogspot.com/2011/09/how-to-split-single-column-value-into.html"]http://manikandaprabhum.blogspot.com/2011/09/how-to-split-single-column-value-into.html[/url]

link

can any one understand that

[cherlapalli_jailer]

that should work
do like this




insert into tbl (
select id , split ikkafa vaadu)

then delete all the records with "|"

[cherlapalli_jailer]

select REGEXP_SUBSTR (txt, '[^|]+', 1, level)
from t
connect by level <= length(regexp_replace(txt,'[^|]*'))+1

[Silver_mani]

[quote name='cherlapalli_jailer' timestamp='1374181701' post='1303974437']

col A val same vundali for those data it seems

[color=#282828][font=helvetica, arial, sans-serif]1 E903|0004[/font][/color]

should be

[color=#282828][font=helvetica, arial, sans-serif]1 E903[/font][/color]
[color=#282828][font=helvetica, arial, sans-serif]1 0004[/font][/color]
[/quote]

Cheyyochu bhayya ...Col A should be the key and the newcolumn which we split the data should be pivoted ....appudu exactly paina chupinchina example ala vastundi

[Silver_mani]

[quote name='massmaharaj' timestamp='1374184070' post='1303974802']
/* Formatted on 7/18/2013 4:45:42 PM (QP5 v5.163.1008.3004) */
SELECT id, REGEXP_SUBSTR (list, '[^,]+', 1, rn) split
FROM (SELECT id, list FROM r1)
CROSS JOIN
( SELECT ROWNUM rn
FROM (SELECT MAX (LENGTH (REGEXP_REPLACE (list, '[^,]+'))) + 1 ax_value
FROM (SELECT id, list FROM r1))
CONNECT BY LEVEL <= max_value)
WHERE REGEXP_SUBSTR (list,
'[^,]+',
1,
rn)
IS NOT NULL
ORDER BY id


i found this in google

[url="http://manikandaprabhum.blogspot.com/2011/09/how-to-split-single-column-value-into.html"]http://manikandaprabhum.blogspot.com/2011/09/how-to-split-single-column-value-into.html[/url]

link

can any one understand that
[/quote]

REGEXP_SUBSTR , REGEXP_REPLACE ... maaka kirkiri ivem functions ??

[loveindia]

sql server lo aitey oka simple function create chesi vestey vachestundi mottam data... adi oracle lo vadoccha ledaa anedi I am not sure... if you want in sql server then tell me I can give u working example...

[prayaanam]

Excel lo picha simple ga cheyyachu........