Anti-pivot
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