Re: Anti-pivot
Date: Mon, 21 Apr 2008 22:23:14 +0200
Message-ID: <fuisvv$u1h$1@online.de>
MountainOaf_at_gmail.com 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
Received on Mon Apr 21 2008 - 15:23:14 CDT