How to join two tables and get the data

Question

I have the two tables as shown below.

Table #1: customer group table:

No    Reg                     Name 
----------------------------------------
111   Account Owner           Josh
111   Customer Group          Josh Group

Table #2: Customer table:

No    Name            Address
----------------------------------
111   Josh Ltd        Lala Land

How can I write a query that returns data like shown here:

No      Customer    Account_Owner  Customer_Group
--------------------------------------------------
111     Josh ltd    Josh           Josh Group

Please assist

Answer 1

Use conditional aggregation:

select c.no, c.name as customer,
    max(case when cg.reg = 'Account Owner'  then name end) as account_owner,
    max(case when cg.reg = 'Customer Group' then name end) as customer_group
from customer c
inner join customer_group cg on cg.no = c.no
group by c.no, c.name