CODEWITHSUNDEEP
sqloracle-databaseoracle11g
tchoedak
tchoedak

Reputation: 97

querying a key - value pair table with conditionals

I've been stumped on this one and would appreciate any help.

Here is the structure of the table:

+----+-------------+-------------------+--+
| id |    name     |       value       |  |
+----+-------------+-------------------+--+
|  3 | email       | [email protected] |  |
|  3 | device_type | iOS               |  |
|  3 | text        | purchase          |  |
|  4 | email       | [email protected]    |  |
|  4 | device_type | iOS               |  |
|  5 | email       | [email protected]  |  |
|  5 | device_type | Android           |  |
|  5 | text        | where can i pur   |  |
+----+-------------+-------------------+--+

I would like a result that looks like this:

╔════╦══════╦═════════════════╦══╦══╗
║ id ║ name ║      value      ║  ║  ║
╠════╬══════╬═════════════════╬══╬══╣
║  3 ║ text ║ purchase        ║  ║  ║
║  4 ║ text ║ Null            ║  ║  ║
║  5 ║ text ║ where can i pur ║  ║  ║
╚════╩══════╩═════════════════╩══╩══╝

Because of the structure of the data in k/v pairs, I'm having trouble finding the right logic to grab unique id's and then the row where the name = text along with the value of text. When a row with the name text does not appear for an id, I would like to still keep that id but show that it doesn't exist.

Upvotes: 0

Views: 201

Answers (2)

Kirill Leontev
Kirill Leontev

Reputation: 10941

SQL Fiddle

select id, 'text', min(decode(name, 'text',value)) 
  from table1 
group by id
order by 1

Results:

| ID | 'TEXT' | MIN(DECODE(NAME,'TEXT',VALUE)) |
|----|--------|--------------------------------|
|  3 |   text |                       purchase |
|  4 |   text |                         (null) |
|  5 |   text |                where can i pur |

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231791

One option would be something like this. I'm getting the distinct ID values from the table in the WITH clause and then left joining back to the table to get the data.

WITH distinct_ids 
  AS( SELECT DISTINCT id
        FROM your_table )
SELECT ids.id,
       nvl(tbl.name,'text'),
       tbl.value
  FROM distinct_ids ids
       LEFT OUTER JOIN your_table tbl
         ON(     ids.id = tbl.id
             AND tbl.name = 'text' )

Upvotes: 0

Related Questions