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 Key Value
------- ------ -----
1 MY_KEY 1
2 MY_KEY 1,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 Key Value
------- ------ -----
2 MY_KEY 1
2 MY_KEY 2
2 MY_KEY 3

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

On Apr 21, 7:44*am, Mountain... (AT) gmail (DOT) com wrote:
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

On Apr 21, 7:44*am, Mountain... (AT) gmail (DOT) com wrote:
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

On Apr 21, 7:44*am, Mountain... (AT) gmail (DOT) com wrote:
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

On Apr 21, 7:44*am, Mountain... (AT) gmail (DOT) com wrote:
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

On Apr 21, 10:18*am, "rogergor... (AT) gmail (DOT) com" <rogergor... (AT) gmail (DOT) com>
wrote:
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 --

On Apr 21, 10:18*am, "rogergor... (AT) gmail (DOT) com" <rogergor... (AT) gmail (DOT) com>
wrote:
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 --

On Apr 21, 10:18*am, "rogergor... (AT) gmail (DOT) com" <rogergor... (AT) gmail (DOT) com>
wrote:
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 --

On Apr 21, 10:18*am, "rogergor... (AT) gmail (DOT) com" <rogergor... (AT) gmail (DOT) com>
wrote:
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 --

MountainOaf (AT) gmail (DOT) com schrieb:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select * from t;

USER_ID KEY VALUE
---------- ---------- --------------------
1 one 1
2 one 1,2,3,4
3 two 1,2
4 one 11,21,313,414

SQL> select user_id, key,
2 rtrim(substr(val, 1, instr(val, ',')), ',') as val
3 from (
4 select user_id, key,
5 substr(val, instr(val, ',', 1, level) + 1) as val
6 from (
7 select user_id, key, ',' || value || ',' as val,
8 nvl(length(replace(translate(value,
9 '1234567890 ',
10 ' '),
11 ' ')), 0) as cc
12 from t)
13 connect by prior user_id = user_id
14 and prior key = key
15 and prior dbms_random.value is not null
16 and level <= cc + 1);

USER_ID KEY VAL
---------- ---------- ----------------------
1 one 1
2 one 1
2 one 2
2 one 3
2 one 4
3 two 1
3 two 2
4 one 11
4 one 21
4 one 313
4 one 414

11 Zeilen ausgewõhlt.

hth,
Urs Metzger