CODEWITHSUNDEEP
sqlsql-serverpivot
santhosha
santhosha

Reputation: 440

Group by in PIVOT operator

How do i use the group by clause in the PIVOT operator?

enter image description here

I tried with the following code but i get the null values and the results are not getting aggregated.

select  EmpName, CHN,HYD FROM location
PIVOT (Sum(salary)  for EmpLoc in ([CHN], [HYD]))
AS 
pivottable

I want the final output to be like this.

        CHN HYD
kunder  400 200
shetty  150 150

or

        CHN HYD Total
kunder  400 200 600
shetty  150 150 300
Total   550 350 900

Upvotes: 2

Views: 267

Answers (3)

Chanukya
Chanukya

Reputation: 5893

Declare @YourTable table (EmpLoc varchar(25),EmpName varchar(25),Salary int)
Insert Into @YourTable values
('HYD','kunder',200)
,('HYD','shetty',150)
,('CHN','shetty',150)
,('CHN','kunder',200)
,('CHN','kunder',200)


;with cte as
(
SELECT * from 
            (
                select *  from @YourTable
           ) as y
            pivot 
            (
                 sum(salary)
                for EmpLoc in ([CHN], [HYD])
            ) as p
            )
SELECT   
    EmpName,sum(CHN)CHN ,sum(HYD)HYD 

FROM CTE  
GROUP BY EmpName;

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

Just add the derived column Total=CHN+HYD and a sub-query to create the Total Row

The Seq (though not displayed) will put the Total row at the bottom

Declare @YourTable table (EmpLoc varchar(25),EmpName varchar(25),Salary int)
Insert Into @YourTable values
('HYD','kunder',200)
,('HYD','shetty',150)
,('CHN','shetty',150)
,('CHN','kunder',200)
,('CHN','kunder',200)


Select EmpName, CHN,HYD,Total=CHN+HYD
 From ( 
        Select Seq=0,EmpLoc,EmpName,Salary From @YourTable
        Union All
        Select Seq=1,EmpLoc,'Total',Salary From @YourTable
      ) A
   pivot (sum(Salary) for EmpLoc in ([CHN], [HYD])) P

Returns

EmpName CHN HYD Total
kunder  400 200 600
shetty  150 150 300
Total   550 350 900

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

I have no issue using your code from your example to get your desired results. I am guessing that your query is not as simple as your example, and as such is introducing other complications not shown here.

You may need to use a subquery and pivot using just the columns necessary for the pivot and join back to the rest of your query to get the results you are looking for using pivot().

Using conditional aggregation may be a simpler solution:

select 
    empname
  , CHN = sum(case when emploc = 'CHN' then salary else 0 end)
  , HYD = sum(case when emploc = 'HYD' then salary else 0 end)
  --, Total = sum(salary) /* Optional total */
from location
group by empname

rextester demo: http://rextester.com/LYRH81756

returns:

+---------+-----+-----+
| EmpName | CHN | HYD |
+---------+-----+-----+
| kunder  | 400 | 200 |
| shetty  | 150 | 150 |
+---------+-----+-----+

Upvotes: 0

Related Questions