Solved

SQL Query. Results sql table split into 2 columns

Posted on 2009-05-06
1
387 Views
Last Modified: 2012-05-06
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
Comment
Question by:bendretread
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 24318396
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question