On Apr 21, 10:18=A0am, "rogergor...@[EMAIL PROTECTED]
" <rogergor...@[EMAIL PROTECTED]
>
wrote:
> On Apr 21, 7:44=A0am, Mountain...@[EMAIL PROTECTED]
wrote:
>
>
>
>
>
> > Hello,
>
> > I have a table which has 3 columns: user id, key and value. A user may
> > only have one instance of a given key. However, the key may have
> > multiple values; these are held as comma-separated values within the
> > value column (there can be any number of these values in the row). An
> > example:
>
> > User_id =A0Key =A0 =A0 Value
> > ------- =A0------ =A0-----
> > 1 =A0 =A0 =A0 =A0MY_KEY =A01
> > 2 =A0 =A0 =A0 =A0MY_KEY =A01,2,3
>
> > Firstly: yes, I know this is *extremely* hideous database design; I
> > cringed when I saw it. Unfortunately, the design is set in stone and I
> > not able to change it.
>
> > The question is, is there any way of getting out the values with one
> > row
> > for each in SQL*Plus? So, for example, getting the values for key
> > MY_KEY
> > for user_id 2 would produce:
>
> > User_id =A0Key =A0 =A0 Value
> > ------- =A0------ =A0-----
> > 2 =A0 =A0 =A0 =A0MY_KEY =A01
> > 2 =A0 =A0 =A0 =A0MY_KEY =A02
> > 2 =A0 =A0 =A0 =A0MY_KEY =A03
>
> > I've had a quick look at pivoting queries but they seem to be doing
> > the
> > opposite of what I'm doing here.
>
> > Version: 10.2.0.1.0
>
> > Many thanks,
>
> > Oaf
>
> You might want to look into REGEXP in Oracle 10G, or if that's too
> uncomfortable, create a view, using some Pl/SQL to populate the table.
>
> Either way, that datamodel is going to be a huge pain in the *** to
> work with and will only cause more problems as it won't scale.
>
> Roger Gorden- Hide quoted text -
>
> - Show quoted text -
Well here is one possible technique to extract the data from a
column. Warning I have tested beyound this simple query.
UT1 > drop table t;
Table dropped.
UT1 > create table t (fld1 number, fld2 varchar2(10), fld3
varchar2(20) );
Table created.
UT1 > insert into t values (1,'one','1');
1 row created.
UT1 > insert into t values (2,'one','1,2,3,4');
1 row created.
UT1 > insert into t values (3,'two','1,2');
1 row created.
UT1 > insert into t values (4,'one','11,21,313,414');
1 row created.
UT1 > col fld2 format a4
UT1 > col fld3 format a13
UT1 > col fld4 format a4
UT1 > col fld5 format a4
UT1 > col fld6 format a4
UT1 > col fld7 format a4
UT1 > select fld1, fld2, fld3,
2 case when instr(fld3,',',1,1) > 0
3 then substr(fld3,1,instr(fld3,',',1) - 1)
4 else fld3
5 end as fld4,
6 case when instr(fld3,',',1,1) > 0
7 then substr(fld3,instr(fld3,',',1,1) + 1,
8 case when instr(fld3,',',1,2) > 0
9 then instr(fld3,',',1,2) - 1 -
instr(fld3,',',1,1)
10 else length(fld3)
11 end )
12 else NULL
13 end as fld5,
14 case when instr(fld3,',',1,2) > 0
15 then substr(fld3,instr(fld3,',',1,2) + 1,
16 case when instr(fld3,',',1,3) > 0
17 then length(fld3) - instr(fld3,',',1,3)
18 else length(fld3)
19 end )
20 else NULL
21 end as fld6,
22 case when instr(fld3,',',1,3) > 0
23 then substr(fld3,instr(fld3,',',1,3) +
1,length(fld3))
24 else NULL
25 end as fld7
26 from t
27 order by fld1, fld2
28 /
FLD1 FLD2 FLD3 FLD4 FLD5 FLD6 FLD7
---------- ---- ------------- ---- ---- ---- ----
1 one 1 1
2 one 1,2,3,4 1 2 3 4
3 two 1,2 1 2
4 one 11,21,313,414 11 21 313 414
The MERGE statement can be used to unpivot data. With 11g Oracle
provides a pivot and unpivot statement.
HTH -- Mark D Powell --
|
