CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
user8839166
user8839166

Reputation:

SQL I want to get the following output

I have a sales table 

Date_s sales_man product qty
1-Jan-18   xx    01       30
1-Jan-18   xx    01       20
1-Jan-18   xy    01       20 
1-Feb-18   xz    02       10
5-Feb-18   xz    02       30
1-Feb-18   xx    01       10
1-Feb-18   xx    01       40
1-Mar-18   xy    03       20

I want to get the following output data format as 

Product  sales_man  Jan  Feb   Mar
01       xx         50    10    0 
01       xy         20    0     0 
02       xx         0     0     0
02       xy         0     0     0
02       xz         0     0     0
03       xy         0     0     20

Upvotes: 1

Views: 87

Answers (3)

Ved Prakash Tiwari
Ved Prakash Tiwari

Reputation: 267

If i got Your problem right, you can try:

SELECT * into #TempTable FROM 
(select product, salesman, qty, FORMAT(Dates, 'MMM') as Months from sales
) AS s 
PIVOT 
(
   SUM(qty)
   FOR Months in (Jan, Feb)
) AS Pvt



select product, salesman, isnull(Jan, 0) as Jan, isnull(Feb, 0) as Feb from 
#TempTable order by product

drop table #TempTable

Upvotes: 0

Nirali
Nirali

Reputation: 1786

If you can change your date format from '1-Jan-18' to '1-01-18', then you can do below way

check sql fiddle

http://sqlfiddle.com/#!9/6d3824/2

If you consider below date format '1-01-18', '1-01-18', '1-02-18';

then you can do this query

select product, salesman,
       sum(case when extract(month from dates) = 1 then qty else 0 end) as jan,
       sum(case when extract(month from dates) = 2 then qty else 0 end) as feb
from sales
group by product, salesman;

Result

product salesman    jan feb
1       xx          30  0
1       xz          0   20
2       xy          10  0

For all month

http://sqlfiddle.com/#!9/6d3824/9

for all do query like this,

select product, salesman,
       sum(case when extract(month from dates) = 1 then qty else 0 end) as jan,
       sum(case when extract(month from dates) = 2 then qty else 0 end) as feb,
       sum(case when extract(month from dates) = 3 then qty else 0 end) as mar,
       sum(case when extract(month from dates) = 4 then qty else 0 end) as apr,
       sum(case when extract(month from dates) = 5 then qty else 0 end) as may,
       sum(case when extract(month from dates) = 6 then qty else 0 end) as jun,
       sum(case when extract(month from dates) = 7 then qty else 0 end) as jul,
       sum(case when extract(month from dates) = 8 then qty else 0 end) as aug,
       sum(case when extract(month from dates) = 9 then qty else 0 end) as sep,
       sum(case when extract(month from dates) = 10 then qty else 0 end) as oct,
       sum(case when extract(month from dates) = 11 then qty else 0 end) as nov,
       sum(case when extract(month from dates) = 12 then qty else 0 end) as dece
from sales
group by product, salesman;

Result would be

product salesman    jan feb mar apr may jun jul aug sep oct nov dece
1       xx          30  0   0   0   0   0   0   0   0   0   0   0
1       xz          0   20  0   0   0   0   0   0   0   0   0   0
2       xy          10  0   0   0   0   0   0   0   0   0   0   0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use aggregation. Something like this:

select product, salesperson,
       sum(case when extract(month from dates) = 1 then qty else 0 end) as jan,
       sum(case when extract(month from dates) = 2 then qty else 0 end) as feb
from t
group by product, salesperson;

This uses ANSI SQL date functions, because your database tag is not clear. Date operations can differ by database.

Also, when looking at data by month, typically, you want to take the year into account as well (by filtering or aggregating on the year).

Upvotes: 1

Related Questions