Re: Anti-pivot

On Apr 21, 7:44 am, Mountain..._at_gmail.com 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  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

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 Received on Mon Apr 21 2008 - 09:18:59 CDT