Kent - 1 year ago 67

SQL Question

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:**

`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 Source

Try using conditional `SUM()`

```
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**