CODEWITHSUNDEEP
sqloracle-databaseplsql
Pinu
Pinu

Reputation: 7520

sql how to transform data vertically

I have a 3 datbles Dealer, payment_type and dealer_payment_type

  Dealer : dealer_id , dealer_name, dealer_address
              1      | test      |  123 test lane
              2      | abc       |  abc lane  
              3      | def       |  def lane


 Payment_type : paymenttype_id , paytype 
                   1           | CHECK
                   2           | WIRE
                   3           | CREDIT

  Dealer_Payment_type : DPT_id , dealer_id , payment_type_id
                         1     |   1       | 1
                         2     |   1       | 2
                         3     |   1       | 3
                         4     |   2       | 2
                         5     |   2       | 3
                         6     |   3       | 1
                         7     |   3       | 2

I have to write a query to get payment type info for each dealer , query needs to return data like this:

         dealer_id , dealer_name , paytype           
           1       |  test       | check,wire,credit
           2       |  abc        | wire,credit
           3       |  def        | check,wire

                    OR

      dealer_id , dealer_name  , check , wire , credit
       1        | test         | true  | true  | true 
       2        | abc          | false | true  | true 
       3        | def          | true  | false | true

Upvotes: 0

Views: 549

Answers (1)

Taryn
Taryn

Reputation: 247820

You did not specify what version of Oracle you are using.

If you are using Oracle 11g, then you can use the following.

To get the values into a single column, then you can use LISTAGG:

select d.dealer_id,
  d.dealer_name,
  listagg(p.paytype, ',') within group (order by d.dealer_id) as paytype
from dealer d
left join Dealer_Payment_type dp
  on d.dealer_id = dp.dealer_id
left join payment_type p
  on dp.payment_type_id = p.paymenttype_id
group by d.dealer_id, d.dealer_name;

See SQL Fiddle with demo

To get the values in separate columns, then you can use PIVOT:

select dealer_id, dealer_name,
  coalesce("Check", 'false') "Check",
  coalesce("Wire", 'false') "Wire",
  coalesce("Credit", 'false') "Credit"
from
(
  select d.dealer_id,
    d.dealer_name,
    p.paytype, 
    'true' flag
  from dealer d
  left join Dealer_Payment_type dp
    on d.dealer_id = dp.dealer_id
  left join payment_type p
    on dp.payment_type_id = p.paymenttype_id
)
pivot
(
  max(flag)
  for paytype in ('CHECK' as "Check", 'WIRE' as "Wire", 'CREDIT' as "Credit")
)

See SQL Fiddle with Demo.

If you are not using Oracle 11g, then you can use wm_concat() to concatenate the values into a single row:

select d.dealer_id,
  d.dealer_name,
  wm_concat(p.paytype) as paytype
from dealer d
left join Dealer_Payment_type dp
  on d.dealer_id = dp.dealer_id
left join payment_type p
  on dp.payment_type_id = p.paymenttype_id
group by d.dealer_id, d.dealer_name;

To create the separate columns, then you can use an aggregate function with a CASE:

select dealer_id, dealer_name,
  max(case when paytype = 'CHECK' then flag else 'false' end) "Check",
  max(case when paytype = 'WIRE' then flag else 'false' end) "Wire",
  max(case when paytype = 'CREDIT' then flag else 'false' end) "Credit"
from
(
  select d.dealer_id,
    d.dealer_name,
    p.paytype, 
    'true' flag
  from dealer d
  left join Dealer_Payment_type dp
    on d.dealer_id = dp.dealer_id
  left join payment_type p
    on dp.payment_type_id = p.paymenttype_id
)
group by dealer_id, dealer_name;

See SQL Fiddle with Demo

Upvotes: 2

Related Questions