Question: I have below 2 tables and I have to, write a query to display the sum of the total amount received through the credit card and cheque. Give the alias names as 'total\_credit' and 'total\_cheque' respectively.

table\_payment  

id (PK)  

user\_id  

amount  

transaction\_type\_id (FK)

table\_transaction\_type  

id (PK)  

type 

Expected Output:

```
total_credit| total_cheque     
Value a     | Value B

```

I tried with below query but I getting values in different rows

```
select  sum(case when tt.type = 'Credit Card' then pt.amount end) as
"total_credit",  sum (case when tt.type = 'Cheque' then pt.amount end)
as "total_cheque" from payment pt join transaction_type tt on
pt.transaction_type_id = tt.id group by tt.type

```

Actual\_Output:

```
total_credit| total_cheque     
Value A     |     
            | Value B

```

not sure how to get Value B in first row along with Value A, please advice.

Answer: 
What you're looking to do is "pivot" the data, and Oracle provides a pivoting function:

```
WITH 
   T 
AS 
(
   select tt.type, pt.amount
     from payment pt
     join transaction_type tt on tt.id = pt.transaction_type_id
)
SELECT 
   *
FROM 
   T
PIVOT 
(
   SUM(amount) 
   FOR 
      (type) 
   IN 
      ('Credit Card', 'Cheque')
);

```

Further examples can be found here: <http://www.dba-oracle.com/t_pivot_examples.htm>
```
select  (SELECT sum(pt.amount FROM from payment pt 
join transaction_type tt on pt.transaction_type_id = tt.id 
WHERE tt.type = 'Credit Card'
group by tt.type) as "total_credit"
, (SELECT sum(pt.amount FROM from payment pt 
join transaction_type tt on pt.transaction_type_id = tt.id 
WHERE tt.type = 'Cheque'
group by tt.type) as "total_cheque"

```

I considered using it as two sub queries, but it looks like we're not joining on any other ID or anything. 

Full disclosure: I'm not familiar with Oracle, but this query will definitely work in T-SQL, and I believe it would work in MySQL, so I don't see why it wouldn't work in Oracle, too.