• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

SQL Query. Results sql table split into 2 columns

I'm trying to query data for a report. Basically I need name, ssn, 401k % contributon, ROTH % Contibution. Problem is that 401k & ROTH are stored in the same column... The statement below is partially complete. What I need to do is add another column that returns results for ROTH, with combining 401K & ROTH.

Hopefully this makes sense... Thanks!
SELECT     UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM, SUM(UPR00500.DEDNPRCT_1) AS [401K]
FROM         UPR00500 INNER JOIN
                      UPR00100 ON UPR00500.EMPLOYID = UPR00100.EMPLOYID
WHERE     (UPR00100.INACTIVE = 0) AND (UPR00500.INACTIVE = 0) AND (UPR00500.DEDUCTON LIKE '401K%')
GROUP BY UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM

Open in new window

0
bendretread
Asked:
bendretread
1 Solution
 
reb73Commented:
Something like this, perhaps?
SELECT
	UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM,
	SUM(CASE WHEN UPR00500.DEDUCTON LIKE '401K%' THEN UPR00500.DEDNPRCT_1 ELSE 0 END) AS [401K],
	SUM(CASE WHEN UPR00500.DEDUCTON LIKE 'ROTH%' THEN UPR00500.DEDNPRCT_1 ELSE 0 END) AS [ROTH]
FROM
	UPR00500
INNER JOIN
	UPR00100 ON UPR00500.EMPLOYID = UPR00100.EMPLOYID
WHERE
	UPR00100.INACTIVE = 0 AND UPR00500.INACTIVE = 0 AND (UPR00500.DEDUCTON LIKE '401K%' OR UPR00500.DEDUCTON LIKE 'ROTH%' )
GROUP BY
	UPR00100.LASTNAME, UPR00100.FRSTNAME, UPR00100.SOCSCNUM

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now