Data Experts Help Needed Please Help Me

[ramu]

pani complete ayindha leda...

[mtkr]

ivi chudu oka saariii...



[url="https://forums.oracle.com/thread/2548667"]https://forums.oracle.com/thread/2548667[/url]


[url="https://forums.oracle.com/thread/1008026"]https://forums.oracle.com/thread/1008026[/url]

[Jatka Bandi]

[quote name='prayaanam' timestamp='1374188055' post='1303975288']
Excel lo picha simple ga cheyyachu........
[/quote] JMP kuda vadachu

[mtkr]

[quote name='bantrothu bullabbai' timestamp='1374196547' post='1303975703']
JMP kuda vadachu
[/quote]
adigindhi cheppandi vayya.... daantlo cheyyochu deentlo cheyyochuuu!!!!

[Jatka Bandi]

[quote name='mtkr' timestamp='1374196845' post='1303975723']
adigindhi cheppandi vayya.... daantlo cheyyochu deentlo cheyyochuuu!!!!
[/quote]license vundo ledo cheppali kada.... vundi ante appudu cheppagalam.... ne laga bewarse anukunnava avasaram lekunna time waste chesukuni type cheyadaniki..... [img]http://stream1.gifsoup.com/view3/1789165/brahmi-beat-by-nag-o.gif[/img]

[massmaharaj]

naaku unde license only oracle , inforamtica


ee roju artham chesukondaaaniki try cehsthaa

[mettastar]

WITH SRC_DATA AS
(
SELECT 1 COLA, 'E903|0004' COLB FROM dual
UNION
SELECT 2 COLA, 'E001|E004|005|006' COLB FROM dual
UNION
SELECT 3 COLA, '3395|8484|39843' COLB FROM dual
)
SELECT COLA,
CASE WHEN CJT.LVL=1 THEN regexp_substr(SRC_DATA.COLB,'[^|]+',1,1)
WHEN CJT.LVL=2 THEN regexp_substr(SRC_DATA.COLB,'[^|]+',1,2)
WHEN CJT.LVL=3 THEN regexp_substr(SRC_DATA.COLB,'[^|]+',1,3)
End as PIV_COL
from
SRC_DATA
cross join (select rownum as lvl from (select 1 from dual group by cube(2,4,8,16)) where rownum<=4) CJT
order by COLA

[mettastar]

SRC_DATA ane table nenu create chesa with ur example data.. ikkada okka limitation enti ante max num of pipes enni untayo neku telisthe saripothundi...

SELECT COLA,
CASE WHEN CJT.LVL=1 THEN regexp_substr(SRC_DATA.COLB,'[^|]+',1,[u][b]1[/b][/u])
WHEN CJT.LVL=2 THEN regexp_substr(SRC_DATA.COLB,'[^|]+',1,[u][b]2[/b][/u])
WHEN CJT.LVL=3 THEN regexp_substr(SRC_DATA.COLB,'[^|]+',1,[u][b]3[/b][/u])
End as PIV_COL
from
Source_TABLE SRC_DATA
cross join (select rownum as lvl from (select 1 from dual group by cube(2,4,8,16)) where rownum<=[color=#ff0000][b]4[/b][/color]) CJT
order by COLA

paina SQL lo SOURCE_TABLE anedi nee source table and replace that 4 with max number of pipes can be expected in that second col (okavela adi kuda teliyakapothe ~999 petteyi),
Case statement lo max no.of pipes ennoo anni substrings chesukuntu vellu... under line chesina number increment chesukuntu vellu with new when clause

[mettastar]

okavela Oracle 11g aithe direct ga Unpivot function vadachu nuvvu.. refer below
[url="http://www.orafaq.com/wiki/UNPIVOT"]http://www.orafaq.com/wiki/UNPIVOT[/url]

[massmaharaj]

vachindi friends andariki thank you a lot

select unique_call_id ,date_key , regexp_substr(pattern_path,'[^|]+',1,r) PATH , r from (
select f.unique_call_id,f.date_key , d.pattern_path from d_call_pattern d, (select pattern_key , unique_call_id ,date_key from f_transactions

unpivot
(pattern_key for (source) in ( pattern1_key, pattern2_key, pattern3_key))
where date_key>=TO_CHAR (SYSDATE - 1, 'yyyymmdd')
) f
where f.pattern_key=d.pattern_key )
,
(select rownum r from dual connect by rownum <= 100) max_users
where
r<= length(regexp_replace(pattern_path,'[^|]')) +1 -- stop condition for max_users
and regexp_substr(pattern_path,'[^|]+',1,r) is not null -- show only real values

aa regexp functions artham cheesukolekapothunnaa

i replaced | instead of ,

[cherlapalli_jailer]

oh good job

[massmaharaj]

mettastar
Silver_mani
cherlapalli_jailer
loveindia
prayaanam
RockStarZ
mtkr
bantrothu bullabbai


Specail thanks to all of you espeailly jailer

Thanks a lot for helping in this situation

[mettastar]

cool

[pachimirchi]

already ochesinda chuskoledu vaya.. aina rasina evaraina vadukondi

--create table #t (id int ,product varchar(255))
--Insert #t values(1,'201001|vbjv8282|cevihiii3939')
--Insert #t values(2,'203101|vbjv8282|cevihiii3939')
--Insert #t values(3,'3001|vbjv8282|cevihiii3939|')
--Insert #t values(4,'vjbj929231|vbjv8282')



;with cte(id,productstring,rn,remstr)
as
(
select id,substring(product,1,charindex('|',product)-1),1,substring(product,charindex('|',product)+1,len(product)-charindex('|',product)) from #t
union all
select id,substring(remstr,1,case when charindex('|',remstr)=0 then len(remstr) else charindex('|',remstr)-1 end),rn+1,case when charindex('|',remstr)=0 then null else substring(remstr,charindex('|',remstr)+1,len(remstr)-charindex('|',remstr)) end from cte where len(remstr)>0
)
select id,productstring from cte order by id,rn

[Silver_mani]

[quote name='massmaharaj' timestamp='1374252681' post='1303979020']
vachindi friends andariki thank you a lot

select unique_call_id ,date_key , regexp_substr(pattern_path,'[^|]+',1,r) PATH , r from (
select f.unique_call_id,f.date_key , d.pattern_path from d_call_pattern d, (select pattern_key , unique_call_id ,date_key from f_transactions

unpivot
(pattern_key for (source) in ( pattern1_key, pattern2_key, pattern3_key))
where date_key>=TO_CHAR (SYSDATE - 1, 'yyyymmdd')
) f
where f.pattern_key=d.pattern_key )
,
(select rownum r from dual connect by rownum <= 100) max_users
where
r<= length(regexp_replace(pattern_path,'[^|]')) +1 -- stop condition for max_users
and regexp_substr(pattern_path,'[^|]+',1,r) is not null -- show only real values

aa regexp functions artham cheesukolekapothunnaa

i replaced | instead of ,
[/quote]


GP buddy .............