Query Db table with Key Value [message #663243] |
Fri, 26 May 2017 15:54 |
|
hippylou2u
Messages: 3 Registered: May 2017
|
Junior Member |
|
|
I have a database table and I'm trying to figure out what's the best approach to take....
Here's what my table looks like:
USERID | KEY | VALUE
user1 | key1 | red
user1 | key2 | table
user2 | key1 | blue
user2 | key2 | chair
user3 | key1 | purple
user3 | key2 | couch
I need my results to look like this:
USERID | KEY 1 | KEY 2
user1 | red | table
user2 | blue | chair
user3 | purple | couch
Any idea how I would turn the keys to be the column headings and the values underneath per user?
Thanks so much
|
|
|
|
Re: Query Db table with Key Value [message #663247 is a reply to message #663243] |
Fri, 26 May 2017 18:36 |
|
mikek
Messages: 29 Registered: January 2017
|
Junior Member |
|
|
A possible approach could be to use the PIVOT Command to turn rows into columns.
1st SQL is just the PIVOT Command. 2nd SQL demonstrates how to delimite
the columns from the PIVOT Query to match posted output.
-- Setup
CREATE TABLE test
(
USERID VARCHAR2(10)
,KEY VARCHAR2(10)
,VALUE VARCHAR2(10)
);
INSERT INTO test VALUES('user1', 'key1', 'red');
INSERT INTO test VALUES('user1', 'key2', 'table');
INSERT INTO test VALUES('user2', 'key1', 'blue');
INSERT INTO test VALUES('user2', 'key2', 'chair');
INSERT INTO test VALUES('user3', 'key1', 'purple');
INSERT INTO test VALUES('user3', 'key2', 'couch');
COMMIT;
-- SQL #1 - PIVOT SQL
SELECT userid
,"KEY 1"
,"KEY 2"
FROM (SELECT *
FROM test
PIVOT
(
MAX(value)
FOR KEY IN ( 'key1' AS "KEY 1"
,'key2' AS "KEY 2")
)
)
ORDER BY userid;
-- SQL #1Output
USERID KEY 1 KEY 2
---------- ---------- ----------
user1 red table
user2 blue chair
user3 purple couch
-- SQL #2 to match Output:
SELECT userid
|| ' | '
|| "KEY 1"
|| ' | '
|| "KEY 2" AS "SAMPLE"
FROM (SELECT *
FROM test
PIVOT
(
MAX(value)
FOR KEY IN ( 'key1' AS "KEY 1"
,'key2' AS "KEY 2")
)
)
ORDER BY userid;
-- SQL #2 Output
SAMPLE
------------------------------------
user1 | red | table
user2 | blue | chair
user3 | purple | couch
|
|
|
Re: Query Db table with Key Value [message #663248 is a reply to message #663247] |
Fri, 26 May 2017 20:34 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
I find using PIVOT which implies aggregation a bit artificial on when each value in pivot from list maps to a single row. Analytic LAG would probably be a bit faster:
with t as (
select userid,
key,
lag(value) over(partition by userid order by key) key1,
value key2
from test
)
select userid,
key1,
key2
from t
where key = 'key2'
order by userid
/
USERID KEY1 KEY2
---------- ---------- ----------
user1 red table
user2 blue chair
user3 purple couch
SQL>
SY.
|
|
|
Re: Query Db table with Key Value [message #663278 is a reply to message #663243] |
Sun, 28 May 2017 01:53 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This looks like one of those attribute-value data models, not a relational data model. Oracle is a relational database management system and SQL is intended to manage relational data. If you try to use Oracle+SQL with your key-value design, the results will not be good. You can do it, but the code will be awful and performance worse. Much better either to use a relational data model or to use some other database and language designed for the attribute-value data model.
In my experience, the attribute-value model is often used when the developer says that he wants "a flexible design that can accommodate any future changes" but the truth is that he hasn't done a proper proper data analysis. Java developers seem to be particularly prone to this. Are you absolutely committed to this non-relational design?
|
|
|
|
|
|
|
Re: Query Db table with Key Value [message #663355 is a reply to message #663354] |
Wed, 31 May 2017 06:40 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
how long is the data in the clob. if it under 4k it's easy to just remap it to varcahr2 using available functions. Also in oracle 12 you can push the max size of varchar2 columns in tables to 32k with a minor database update command. What is your exact version.
|
|
|
Re: Query Db table with Key Value [message #663365 is a reply to message #663340] |
Wed, 31 May 2017 11:11 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
hippylou2u wrote on Tue, 30 May 2017 15:55Maybe the lag() function might work?
LAG doesn't support CLOB. There is a "trick" to create object type with CLOB attribute and ORDER method which will tell ORACLE how to order objects which is required when calculating MAX:
SQL> CREATE TABLE test
2 (
3 USERID VARCHAR2(10)
4 ,KEY VARCHAR2(10)
5 ,VALUE CLOB
6 )
7 /
Table created.
SQL> INSERT INTO test VALUES('user1', 'key1', 'red');
1 row created.
SQL> INSERT INTO test VALUES('user1', 'key2', 'table');
1 row created.
SQL> INSERT INTO test VALUES('user2', 'key1', 'blue');
1 row created.
SQL> INSERT INTO test VALUES('user2', 'key2', 'chair');
1 row created.
SQL> INSERT INTO test VALUES('user3', 'key1', 'purple');
1 row created.
SQL> INSERT INTO test VALUES('user3', 'key2', 'couch');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT *
2 FROM test
3 PIVOT(
4 MAX(value)
5 FOR KEY IN (
6 'key1' AS "KEY 1",
7 'key2' AS "KEY 2"
8 )
9 )
10 /
SELECT *
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL> CREATE OR REPLACE
2 TYPE CLOB_OBJ_TYPE
3 IS OBJECT(
4 C CLOB,
5 ORDER MEMBER FUNCTION COMPARE(
6 CLOB_OBJ IN CLOB_OBJ_TYPE
7 )
8 RETURN NUMBER
9 )
10 /
Type created.
SQL> CREATE OR REPLACE
2 TYPE BODY CLOB_OBJ_TYPE
3 IS
4 ORDER MEMBER FUNCTION COMPARE(
5 CLOB_OBJ IN CLOB_OBJ_TYPE
6 )
7 RETURN NUMBER
8 IS
9 BEGIN
10 RETURN DBMS_LOB.COMPARE(
11 SELF.C,
12 CLOB_OBJ.C
13 );
14 END;
15 END;
16 /
Type body created.
SQL> SELECT USERID,
2 T."KEY 1".C "KEY 1",
3 T."KEY 2".C "KEY 2"
4 FROM TEST
5 PIVOT(
6 MAX(CLOB_OBJ_TYPE(value))
7 FOR KEY IN (
8 'key1' AS "KEY 1",
9 'key2' AS "KEY 2"
10 )
11 ) T
12 /
USERID KEY 1 KEY 2
---------- ------------------------------ ------------------------------
user2 blue chair
user1 red table
user3 purple couch
SQL>
SY.
|
|
|