CODEWITHSUNDEEP
sqloracle-databaseoracle11g
ale
ale

Reputation: 11824

Query to turn field names/values rows as a single row

I'm working with a system that allows users to create their own fields. This is implemented in Oracle as a field table and then a field/value table. If I run a query like this:

SELECT 
  field_name, field_value
FROM field_t
INNER JOIN field_t ON field_t.id = field_string_t.field_id
WHERE (field_name = 'foo'
  OR field_name = 'bar'
  OR field_name = 'buzz'
)

then I can get the field name/value pairs in a series of rows:

id      field_name    field_value
8       foo           1
8       bar           5
8       buzz          4
... and so on for each id

How can I write a query to return this instead:

id      foo      bar      buzz
8       1        5        4

?

Thanks.

Upvotes: 1

Views: 165

Answers (1)

psaraj12
psaraj12

Reputation: 5072

As suggested by Mark Bannister you could use the PIVOT function to do the same

If you want to use a sub-query instead of values for field_name use PIVOT XML(Note the result will be in XML in this case)

For more information please check the below link

PIVOT and UNPIVOT operators in Oracle 11g

 select * from
 (
 SELECT id, field_name, field_value,field_id
 FROM   field_t)
 PIVOT  (
 MAX(field_value) 
 FOR (field_name) IN ('foo' as foo, 'bar' AS bar, 'buzz' as buzz))
 WHERE id=field_id;

Upvotes: 1

Related Questions