Re: Anti-pivot

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