Home » SQL & PL/SQL » SQL & PL/SQL » interpreting fields..
interpreting fields.. [message #222543] Mon, 05 March 2007 06:39 Go to next message
srraajesh
Messages: 63
Registered: May 2005
Member
Hi Gurus,

I have a table data like this


Field1
------
1,2,3,4
5,6

Field 2
-------
v1,v2,v3,v4
v5,v6

Now with either SQL/PL SQL I want my output in a single row like this

row 1....v1=1 and v2=2 and v3=3 and v4=4
row2 ....v5=5 and v6=6

How can this be done???
Re: interpreting fields.. [message #223642 is a reply to message #222543] Fri, 09 March 2007 23:36 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Hi srraajesh,

This works ...

set echo on
set feedback off

create table t
( key number(3), field_1 varchar2(15), field_2 varchar2(15) );

insert into t values ( 1, '1,2,3,4', 'v1,v2,v3,v4'  );
insert into t values ( 2, '5,6'    , 'v5,v6'        );
insert into t values ( 3, null     , null           );

commit;

column result format a40

select
  key ,
  field_1 ,
  field_2 ,
  replace
  (
    rtrim
    (
      regexp_replace
        ( field_1 || ',' ,
         '^([^,]+,)?([^,]+,)?([^,]+,)?([^,]+,)?([^,]+,)?([^,]+,)?([^,]+,)?([^,]+,)?([^,]+,)?$' ,
          regexp_replace
            ( replace( field_2||',', ',', '=' ) ,
             '^([^=]+=)?([^=]+=)?([^=]+=)?([^=]+=)?([^=]+=)?$' ,
             '\1\\1\2\\2\3\\3\4\\4\5\\5\6\\6\7\\7\8\\8\9\\9'
            )
        ),
      ','
    ),
    ',',
    ' and '
  )
  as result
from
 t
;

       KEY FIELD_1         FIELD_2         RESULT
---------- --------------- --------------- ----------------------------------------
         1 1,2,3,4         v1,v2,v3,v4     v1=1 and v2=2 and v3=3 and v4=4
         2 5,6             v5,v6           v5=5 and v6=6
         3

drop table t;


I can't say I really like this solution, but then again I don't like saving multiple values in a single cell either so I guess it evens out. Rolling Eyes

--
SnippetyJoe
http://www.sqlsnippets.com/
Re: interpreting fields.. [message #223656 is a reply to message #223642] Sat, 10 March 2007 02:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nice one, although I agree with both your points in your remark Smile
Nice site you got, by the way.
Re: interpreting fields.. [message #223741 is a reply to message #223656] Sat, 10 March 2007 19:36 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Thanks Frank. I appreciate the feedback. My site's still new so there isn't too much content yet, but I've got plenty of topics on the "to do" list.

To the OP, I forgot to mention that the solution only works for a max. of 9 items. If you've got more you'll need to either break the field_% strings into smaller, 9 item fragments or try another approach.

--
SnippetyJoe
http://www.sqlsnippets.com/
Re: interpreting fields.. [message #238116 is a reply to message #223642] Wed, 16 May 2007 14:13 Go to previous message
mohit_agarwal
Messages: 1
Registered: May 2007
Junior Member
Here's another solution that doesn't have the 9 field limitation and might be easier to understand. Basically I take apart the string into rows, join the fields and put them back together.

WITH t AS
       (SELECT 1 id, '1,2,3,4' field1, 'v1,v2,v3,v4' field2 from dual
        UNION ALL
        SELECT 2, '5,6'    , 'v5,v6' FROM dual
        UNION ALL
        SELECT 3, null     , null  FROM dual
        )
   , t2 AS (
SELECT id
     , field1
     , field2
     , REGEXP_SUBSTR(field1, '[^,]+', 1, level) field_1
     , REGEXP_SUBSTR(field2, '[^,]+', 1, level) field_2
     , level lev
     , RANK() OVER(PARTITION BY id ORDER BY LEVEL DESC) rnk
  FROM t
CONNECT BY INSTR(field1, ',', 1, level-1) > 0
   AND id = PRIOR id
   AND PRIOR dbms_random.value IS NOT NULL
       )
SELECT id, field1, field2
     , LTRIM(SUBSTR(SYS_CONNECT_BY_PATH(field_2||'='||field_1, ' and '), 6), '=') result
  FROM t2
 WHERE rnk = 1
CONNECT BY id = PRIOR id
   AND lev = PRIOR lev+1
 START WITH lev = 1;

output is
ID	FIELD1	FIELD2	        RESULT
1	1,2,3,4	v1,v2,v3,v4	v1=1 and v2=2 and v3=3 and v4=4
2	5,6	v5,v6	        v5=5 and v6=6
3			

Previous Topic: could u plz clear my doubts???
Next Topic: Top 1 from Subtable
Goto Forum:
  


Current Time: Sat Dec 10 14:37:59 CST 2016

Total time taken to generate the page: 0.14165 seconds