Anti-pivot

#11

MountainOaf (AT) gmail (DOT) com schrieb:

Quote:

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

#12

MountainOaf (AT) gmail (DOT) com schrieb:

Quote:

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

#13

MountainOaf (AT) gmail (DOT) com schrieb:

Quote:

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

#14

Quote:

This works with Oracle 10g XE and Mark's sample data:

This works a treat - many thanks.

Oaf

#15

Quote:

This works with Oracle 10g XE and Mark's sample data:

This works a treat - many thanks.

Oaf

#16

Quote:

This works with Oracle 10g XE and Mark's sample data:

This works a treat - many thanks.

Oaf

#17

Quote:

This works with Oracle 10g XE and Mark's sample data:

This works a treat - many thanks.

Oaf

#18

On Apr 22, 5:00*am, Mountain... (AT) gmail (DOT) com wrote:

Quote:

This works with Oracle 10g XE and Mark's sample data:

This works a treat - many thanks.

Oaf

Urs, yes very nice plus it finishes the job I started since by the
time I figured out the minor error I made while writing the query and
posted I left out the final pivot of the columns into rows.

-- Mark D Powell --

#19

On Apr 22, 5:00*am, Mountain... (AT) gmail (DOT) com wrote:

Quote:

This works with Oracle 10g XE and Mark's sample data:

This works a treat - many thanks.

Oaf

Urs, yes very nice plus it finishes the job I started since by the
time I figured out the minor error I made while writing the query and
posted I left out the final pivot of the columns into rows.

-- Mark D Powell --

#20

On Apr 22, 5:00*am, Mountain... (AT) gmail (DOT) com wrote:

Quote:

This works with Oracle 10g XE and Mark's sample data:

This works a treat - many thanks.

Oaf

Urs, yes very nice plus it finishes the job I started since by the
time I figured out the minor error I made while writing the query and
posted I left out the final pivot of the columns into rows.

-- Mark D Powell --