CODEWITHSUNDEEP
sqloracle-databasepivot
ab11
ab11

Reputation: 20090

How to combine monthly results into a single row?

If have a sales table with data like:

SALES
-----
seller_id month amount
1         1     10
1         2     15

I would like to retrieve all seller's sales in a single row. How can I combine the results from the table into a result like this?

seller_id    jan_amount    feb_amount  
    1            10            15

EDIT:

I can retrieve the monthly sales with this query:

select    seller_id, month, sum(amount)
from      sales
group by  sellerd_id, month

But it gives me results like:

1,1,10
1,2,15

I'm hoping to transform this into:

1, 10 as JAN_AMOUNT, 15 as FEB_AMONT

Upvotes: 0

Views: 43

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

You may use conditional aggregation to pivot your data with the following SQL statement as :

select seller_id,
       max(case when month = 1 then amount end  ) as jan_amount,
       max(case when month = 2 then amount end  ) as feb_amount
  from sales
 where seller_id = 1
 group by seller_id;

 SELLER_ID  JAN_AMOUNT  FEB_AMOUNT
 ---------  ----------  ----------
    1           10          15

As an Oracle user, you may use the following SQL statement with pivot keyword as :

select *
  from sales
 pivot(
       sum(amount)
       for(month)
        in (1 as jan_amount ,2 as feb_amount )
   );

 SELLER_ID  JAN_AMOUNT  FEB_AMOUNT
 ---------  ----------  ----------
    1           10          15

Rextester Demo

Upvotes: 2

Related Questions