My Query

[vbjohn]

Problem: I am getting multiple records for each DEDUCTON. What I want to do is Get one record and then Break it down.
For Example: What I have...

Name: Deduction: DedAmount:

McGovern, John 024 41.02
McGovern, John 124 20.25
McGovern, John 224 7.94

What I want it to look like.

Name: Deduction: DedAmount: Ded2: DedA2: Ded3:

McGovern, John 024 41.02 124 20.25 224


Here is my Code:

SELECT UPR00300.EMPLOYID, RTRIM(UPR00100.LASTNAME) + ', ' + RTRIM(UPR00100.FRSTNAME) AS NAME, UPR00500.DEDUCTON FROM UPR00300 INNER JOIN UPR00900 ON UPR00300.EMPLOYID = UPR00900.EMPLOYID INNER JOIN UPR00700 ON UPR00300.EMPLOYID = UPR00700.EMPLOYID INNER JOIN UPR00500 ON UPR00300.EMPLOYID = UPR00500.EMPLOYID INNER JOIN UPR00100 ON UPR00300.EMPLOYID = UPR00100.EMPLOYID WHERE (UPR00100.INACTIVE = 0) AND (UPR00500.DEDUCTON = '024') OR (UPR00500.DEDUCTON = '031') OR (UPR00500.DEDUCTON = '124') OR (UPR00500.DEDUCTON = '224') ORDER BY UPR00300.EMPLOYID, RTRIM(UPR00100.LASTNAME) + ', ' + RTRIM(UPR00100.FRSTNAME)

Please let me know how to fix this problem

 

[jjonesal]

What about using something like this:

SELECT RTRIM(lname) + ', ' + RTRIM(fname) as fullname,
SUM(CASE deduction WHEN '024' THEN dedamt ELSE 0 END) AS d1,
SUM(CASE deduction WHEN '124' THEN dedamt ELSE 0 END) AS d2,
SUM(CASE deduction WHEN '224' THEN dedamt ELSE 0 END) AS d3
FROM yourtable
GROUP BY RTRIM(lname) + ', ' + RTRIM(fname) J. Jones
jjones@cybrtyme.com