CODEWITHSUNDEEP
sql-serverpivot
see sharp
see sharp

Reputation: 79

Pivot in sql query

I have this query .This query have union all 

select cm.comp_name,e.Name_Employee,SUM(im.MSalary) MSalary,ID_invoice
    from   MMas im , employee e , company cm
    where              
    e.ID_Employee = im.ID_Employee
    and im.cid = cm.cid
    and im.ID_Employee=74
    group by cm.comp_name,e.Name_Employee , ID_invoice                                                  
    union all    
    select  cm.comp_name, e.Name_Employee,SUM(id.Amount) FBenefits,ID_invoice
    from   MMas im, MDil id,employee e, company cm
    where               
    im.MInd =id.MInd
    and e.ID_Employee=im.ID_Employee
    and cm.cid=im.cid
    and im.ID_Employee=74
     group by cm.comp_name,e.Name_Employee ,ID_invoice

and this shows result like this 

     comp_name        Name_Employee  ID_Employee    MSalary   ID_invoice
ABC_Company            David                        74                1000        98950
ABC_Company          David                          74                  0             98950

where as i want result like this 

comp_name                   Name_Employee  ID_Employee MSalary F_Benefits       ID_invoice
    ABC_Company       David                           74                       1000          0                        98950

how to do this ?

Upvotes: 0

Views: 49

Answers (2)

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please try this -

SELECT comp_name, Name_Employee,ID_Employee,SUM(MSalary) Salary,SUM(FBenefits)F_Benefits,ID_invoice
FROM 
(
    select cm.comp_name,e.Name_Employee,SUM(im.MSalary) MSalary, 0 FBenefits ,ID_invoice
    from   MMas im , employee e , company cm
    where              
    e.ID_Employee = im.ID_Employee
    and im.cid = cm.cid
    and im.ID_Employee=74
    group by cm.comp_name,e.Name_Employee , ID_invoice                                                  
    union all    
    select  cm.comp_name, e.Name_Employee,0 MSalary ,SUM(id.Amount) FBenefits,ID_invoice
    from   MMas im, MDil id,employee e, company cm
    where               
    im.MInd =id.MInd
    and e.ID_Employee=im.ID_Employee
    and cm.cid=im.cid
    and im.ID_Employee=74
    group by cm.comp_name,e.Name_Employee ,ID_invoice
)x 
GROUP BY comp_name, Name_Employee,ID_Employee,ID_invoice

Upvotes: 1

JamieD77
JamieD77

Reputation: 13949

you should be able to get the FBenefits amount using Outer Apply and take out the Union

select  cm.comp_name,
        e.Name_Employee,
        sum(im.MSalary) MSalary,
        id.FBenefits,
        ID_invoice
from    employee e
join    MMas im on e.ID_Employee = im.ID_Employee
join    company cm on im.cid = cm.cid
outer apply (
        select  sum(id.Amount) FBenefits
        from    MDil id
        where   im.MInd = id.MInd
)       id
where   e.ID_Employee = 74
group by cm.comp_name,
        e.Name_Employee,
        id.FBenefits,
        ID_invoice

Upvotes: 0

Related Questions