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
|
