How do you make an Oracle SQL query to...

Question

This table is rather backwards from a normal schema, and I'm not sure how to get the data I need from it.

Here is some sample data,

Value (column)          Info (column)
---------------------------------------------
Supplier_1              'Some supplier'
Supplier_1_email        '[email protected]'
Supplier_1_rating       '5'
Supplier_1_status       'Active'
Supplier_2              'Some other supplier'
Supplier_2_email        '[email protected]'
Supplier_2_rating       '4'
Supplier_2_status       'Active'
Supplier_3              'Yet another supplier'

...

I need a query to find the email of the supplier which has the highest rating and is currently of status 'Active'.

Answer 1

select 
    m.sup_email, r.sup_rating 
from 
    (select substr(value, 1, length(value) - length('_email') as sup_name, info as sup_email from table where value like '%_email') as m 
left join 
    (select substr(value, 1, length(value) - length('_rating') as sup_name), info as sub_rating from table where value like '%_rating') as r on m.sup_name = r.sup_name 
order by 
   sup_rating desc 
limit 
    1;

Answer 2

Expanding on Mike's excellent suggestion:

CREATE VIEW supplier_names AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1) AS supplier_id
        ,Info AS supplier_name
  FROM   the_table
  WHERE  INSTR(Value,'_',1,2) = 0;

CREATE VIEW supplier_emails AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1,INSTR(Value,'_',1,2)-INSTR(Value,'_')-1)
         AS supplier_id
        ,Info AS supplier_email
  FROM   the_table
  WHERE  Value LIKE '%email';

CREATE VIEW supplier_ratings AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1,INSTR(Value,'_',1,2)-INSTR(Value,'_')-1)
         AS supplier_id
        ,Info AS supplier_rating
  FROM   the_table
  WHERE  Value LIKE '%rating';

CREATE VIEW supplier_statuses AS
  SELECT SUBSTR(Value,INSTR(Value,'_')+1,INSTR(Value,'_',1,2)-INSTR(Value,'_')-1)
         AS supplier_id
        ,Info AS supplier_rating
  FROM   the_table
  WHERE  Value LIKE '%status';

The queries will perform like dogs, so I'd suggest you look into creating some virtual columns, or at least function-based indexes, to optimise these queries.

Answer 3

For a single pass solution, try:

select "email" from 
(select 
    substr("value", 1, 8 + instr(substr("value", 10, length("value")-9),'_')) "supplier", 
    max(case when "value" like '%_status' then "info" end) as "status",
    max(case when "value" like '%_rating' then cast("info" as integer) end) as "rating",
    max(case when "value" like '%_email' then "info" end) as "email"
from "table" t
where "value" like '%_rating' or "value" like '%_email' or "value" like '%_status'
group by substr("value", 1, 8 + instr(substr("value", 10, length("value")-9),'_'))
having max(case when "value" like '%_status' then "info" end) = 'Active'
order by 3 desc
) where rownum = 1

(Column names are all double-quoted as some are reserved words.)