Pivot in sql query

Question

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 ?

Pawan Kumar · Accepted Answer · 2018-01-08 08:50:50Z

1

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

edited Jan 8, 2018 at 8:50

answered Jan 5, 2018 at 7:42

Pawan Kumar

2,02112 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pawan Kumar Over a year ago

CTE is just a wrapper for the temporary result set of the SQL present inside. So once you put your sql inside the CTE you can use it in the next statement For more details pls read from learn.microsoft.com/en-us/sql/t-sql/queries/… technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

see sharp Over a year ago

if i dont want to use CTE then how i did this because this seems confusion in your query

JamieD77 · Accepted Answer · 2018-01-05 21:26:24Z

0

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

answered Jan 5, 2018 at 21:26

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Collectives™ on Stack Overflow

Pivot in sql query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related