Home » SQL & PL/SQL » SQL & PL/SQL » Query Db table with Key Value (Oracle)
Query Db table with Key Value [message #663243] Fri, 26 May 2017 15:54 Go to next message
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 #663244 is a reply to message #663243] Fri, 26 May 2017 16:10 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

What should be done when more than 2 rows exist for any USERID value?
What should be done when odd number of rows exist for any USERID value?

Does data in this table conform to Third Normal Form?
Re: Query Db table with Key Value [message #663247 is a reply to message #663243] Fri, 26 May 2017 18:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
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 Go to previous messageGo to next message
John Watson
Messages: 7188
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 #663340 is a reply to message #663278] Tue, 30 May 2017 14:55 Go to previous messageGo to next message
hippylou2u
Messages: 3
Registered: May 2017
Junior Member
Hi,

Unfortunately yes... The database table is already populated and has been running for years. However, I've inherited a project that involves exporting the data into a single row per id. The data scenario that I presented is purely an example. But, when I try to the PIVOT approach I get an error: ORA-00932: inconsistent datatypes: expected - got CLOB. The VALUE is a CLOB datatype so using it in a MAX() function might not work. Maybe the lag() function might work? I'll test it next. If anyone else has any suggestions based on this error I'm receiving I'd appreciate any feedback.

Thanks

Re: Query Db table with Key Value [message #663341 is a reply to message #663340] Tue, 30 May 2017 15:10 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
CLOB?
What CLOB?
I don't see any CLOB.

CREATE TABLE test
(
  USERID    VARCHAR2(10)
 ,KEY       VARCHAR2(10)
 ,VALUE     VARCHAR2(10)
);
Re: Query Db table with Key Value [message #663342 is a reply to message #663341] Tue, 30 May 2017 15:25 Go to previous messageGo to next message
hippylou2u
Messages: 3
Registered: May 2017
Junior Member
The actual data contains a clob as the value.
Re: Query Db table with Key Value [message #663354 is a reply to message #663342] Wed, 31 May 2017 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's the sort of thing you need to tell us up front if you want meaningful help.
Re: Query Db table with Key Value [message #663355 is a reply to message #663354] Wed, 31 May 2017 06:40 Go to previous messageGo to next message
Bill B
Messages: 1701
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 Go to previous message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
hippylou2u wrote on Tue, 30 May 2017 15:55
Maybe 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.
Previous Topic: Armstrong numbers
Next Topic: Creating foreign key constraints for nested tables of user defined types oracle
Goto Forum:
  


Current Time: Sun Dec 17 10:09:39 CST 2017

Total time taken to generate the page: 0.04761 seconds