Kent Kent - 22 days ago 11
SQL Question

Sql Query to bring back all data into one row

So I have been working on this for a while and I thought I would ask the experts. I want to be able to bring my query results back on one row but for the life of me I cannot figure this out. In my databases field I have two deduction codes 2902 and 2903 both equal a different amount. Not all returns will contain 2903 code but most will contain 2902 code. How would I go about bringing the return back so that all the data is in one row for an employee?
The output I am looking for is the following

NameLast , NameFirst , DeductionCode2902, deductionamt2902, deductioncode2903, deductionamt2903

Instead of multiple rows like below:

NameLast, Namefirst,Deductioncode2902, DeductionAmt2902

NameLast,NameFirst, Deductioncode2903, DeductionAmt2903

Data format in Database:

enter image description here

Select
pr_employ.namelast,
pr_employ.namefirst,
pr_employ_hist.deductioncode
pr_employ_hist.deductionamt
from pr_employ, pr_employ_hist
Where pr_employ.namelast = pr_employhist.namelast and
pr_employ_hist.deductioncode = ‘2902’ or pr_employ_hist.deductioncode =’2903’
Group by
pr_employ.namelast,
pr_employ.namefirst,
pr_employ_hist.deductioncode
pr_employ_hist.deductionamt

Answer

Try using conditional SUM()

DEMO

SELECT `namelast`, `namefirst`,
       SUM(CASE WHEN `DeductionCode` = 2902 THEN `DeductionAmt` ELSE 0 END) as amt2902,
       SUM(CASE WHEN `DeductionCode` = 2903 THEN `DeductionAmt` ELSE 0 END) as amt2903
FROM Table1
GROUP BY `namelast`, `namefirst`

OUTPUT

enter image description here