I'm trying to create a new view in an Oracle DB from two tables and need to add different columns from the same source column based on the value of another source column. The source tables are a customer table and another table with additional data. This second table has one column 'metadata content' which contains values I want to distribute to different columns in the view, based on the value of the column 'metadata key'

This is what I have as source tables, very simplified:

Customer Table

CustomerMetadata Table

The source column Metadata Content contains different values regarding business activity or year since active. I need to put these values in the relative column in the view:

Aggregated Customer View

I was able to create the view with one metadata column, but I don't know how to add more and refer to them accordingly. And my current solution shows only rows where the metadataContent is available. If one customer has no activity vale in the metadataContent column (NULL), the view doesn't include the customer at all.

create or replace force View customerview as 
    (SELECT
        customer.CustomerName as CustomerName,
        customer.CustomerID as CustomerID
        CustomerMetadata.MetadataContent as Activity
        
    FROM customer
    
    JOIN CustomerMetadata on customer.CustomerID = CustomerMetadata.CustomerID 
    
    where CustomerMetadata.MetadataKey = '55C'
    
    );

CodePudding user response：

One option is to conditionally aggregate values, presuming that 55C represents activity, while 80A represents establishment year.

Sample data:

SQL> with
  2  customer (customername, customerid) as
  3    (select 'Miller Inc' , 100 from dual union all
  4     select 'Johnson Ltd', 101 from dual
  5    ),
  6  customermetadata (metadatacontent, metadatakey, customerid) as
  7    (select 'plumber'  , '55C', 101 from dual union all
  8     select '1968'     , '80A', 101 from dual union all
  9     select 'carpenter', '55C', 100 from dual union all
 10     select '1982'     , '80A', 100 from dual
 11    )

Query you'd use for the view begins here:

 12  select c.customername,
 13         c.customerid,
 14         max(case when metadatakey = '55C' then metadatacontent end) as activity,
 15         max(case when metadatakey = '80A' then metadatacontent end) as establishedsince
 16  from customer c join customermetadata m on m.customerid = c.customerid
 17  group by c.customername,
 18           c.customerid
 19  /

CUSTOMERNAM CUSTOMERID ACTIVITY  ESTABLISH
----------- ---------- --------- ---------
Johnson Ltd        101 plumber   1968
Miller Inc         100 carpenter 1982

SQL>