MountainOaf@[EMAIL PROTECTED]
schrieb:
> 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
This works with Oracle 10g XE and Mark's sample data:
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
|
