Oracle 11g database has 2 tables:

Items

 ITEM_ID | NAME ================= 1 | first 2 | second

and

ItemFields

 ITEM_ID | FIELD_NAME | VALUE ===================================================== 1 | Field1 | aaa 1 | Field2 | bbb 2 | Field1 | ccc 2 | Field2 | ddd

Each line from Items corresponds to several lines from ItemFields.

How to get a table view

 ITEM_ID | NAME | Field1 | Field2 =================================== 1 | first | aaa | bbb 2 | second | ccc | ddd

I see several options, but I would like to choose the most effective:
1) Use LEFT JOIN and LEFT JOIN through the data in the client application
2) Use the LEFT JOIN and loop through the result in the stored procedure, creating a new resultset
3) Use subqueries

  SELECT i.ITEM_ID, i.NAME, (SELECT VALUE FROM ItemFields WHERE ITEM_ID=i.ITEM_ID AND FIELD_NAME='Field1') as Field1, (SELECT VALUE FROM ItemFields WHERE ITEM_ID=i.ITEM_ID AND FIELD_NAME='Field2') as Field2 FROM Items

4) Use PIVOT (to be honest, I didn’t quite understand how to work with it and whether it can work with several fields: VALUE_INT, VALUE_DATE)

  • I suppose that FIELD_NAME in ItemFields will be added and not known in advance? - AkaInq
  • @AkaInq FIELD_NAME is known in advance. Moreover, in my stored procedure, a specific FIELD_NAME list will be used, not everything. - GreenBee

1 answer 1

I will offer another way:

 SELECT i.ITEM_ID, i.NAME, max(decode(f.FIELD_NAME,'Field1',f.VALUE,NULL)) f1, max(decode(f.FIELD_NAME,'Field2',f.VALUE,NULL)) f2, max(decode(f.FIELD_NAME,'Field3',f.VALUE,NULL)) f3, max(decode(f.FIELD_NAME,'Field4',f.VALUE,NULL)) f4 FROM Items i LEFT JOIN ItemFields f ON f.ITEM_ID=i.ITEM_ID GROUP BY i.ITEM_ID, i.NAME

As for the pivot , you can choose one of the values ​​from different columns using the same decode , but the resulting value should still be only one, which is not as flexible as in the first version. And it will look something like this:

 SELECT * FROM ( SELECT i.ITEM_ID, i.NAME, f.FIELD_NAME, f.VALUE_INT, f.VALUE_DATE FROM Items i LEFT JOIN ItemFields f ON f.ITEM_ID=i.ITEM_ID ) PIVOT ( max(decode(FIELD_NAME,'Field1',VALUE_DATE,'Field3',VALUE_DATE,VALUE_INT)) for FIELD_NAME in('Field1','Field2','Field3','Field4') )

It is difficult to say what the resulting data type of the columns would be, I checked it on a table with a numeric and character field, it looks like the types were preserved, although it was possible that he brought everything to the line.

  • How much more effective is your variant than the subquery variant? - GreenBee
  • 2
    @GreenBee And how do you propose to evaluate it, in what units. Compare plans for one and another options, see the cost. Subqueries in the select list are usually evil because they are poorly optimizable, are re-executed for each row, and just a few calls to the table for the sake of one row. On the other hand, group by is certainly slower than direct reading of only one table, and here the question is rather in the total amount of data. And usually, group by is more efficient than iterating over the cycle and self-accumulating the same data (even in the procedure, and even more so on the client) - Mike