Anti-pivot

From: <MountainOaf_at_gmail.com>
Date: Mon, 21 Apr 2008 04:44:30 -0700 (PDT)
Message-ID: <62f22bb5-06f2-4f48-a7e7-6b54529278a0@a9g2000prl.googlegroups.com>


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 Received on Mon Apr 21 2008 - 06:44:30 CDT

Original text of this message