SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Table Convert Rows to Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wldodds
Starting Member

20 Posts

Posted - 01/16/2013 :  17:20:05  Show Profile  Reply with Quote
I have a table which contains 3 columns (empid, benefit, amount). The primary keys are empid and benefit. An Employee can have many different benefits with different amounts.

empid_i benefit costemployee_i
1331 04000 3.26000
1331 10101 0.00000
1331 13100 0.00000
1331 14140 2.62000
1331 15120 3.59540
1331 16100 0.00000
1331 21610 4.26920

I'm trying to get specific benefits into specific columns. For example I want anything beginning with '14' to be in medcolumn and anything beginning with 15 to be in dencolumn.

Here is the code I'm using but it returns 2 rows for the employee instead of 1 row with the amounts in the specific columns.

SELECT ebm.EMPID_I as Employee_Id,
(case when left(med.benefit,2) = ('14') then med.COSTEMPLOYEE_I
else 0
end) as Medical_Amount,
(case when left(den.benefit,2) = ('15') then den.COSTEMPLOYEE_I
else 0
end) as Dental_Amount
FROM BE010130 ebm (nolock)
inner join BE010130 med (nolock) on ebm.EMPID_I = med.EMPID_I and ebm.BENEFIT = med.BENEFIT
inner join BE010130 den (nolock) on ebm.EMPID_I = den.EMPID_I and ebm.BENEFIT = den.BENEFIT
WHERE
ebm.BENEFITSTATUS_I = 1 -- Active Only
and ebm.BENEFIT in (select BENEFIT from BE020230 ben (nolock) where (ben.DSCRIPTN LIKE 'D/ UHC%' or ben.DSCRIPTN LIKE 'D/ Dental%'))
and ebm.empid_i = 1331

Any help is greatly appreciated.

Thanks,

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/16/2013 :  22:14:49  Show Profile  Reply with Quote
SELECT ebm.EMPID_I as Employee_Id, 
MAX(Case when left(med.benefit,2) = ('14') then med.COSTEMPLOYEE_I else 0 end) as Medical_Amount,
MAX(Case when left(den.benefit,2) = ('15') then med.COSTEMPLOYEE_I else 0 end) as Dental_Amount
FROM BE010130 ebm (nolock)
inner join BE010130 med (nolock) on ebm.EMPID_I = med.EMPID_I and ebm.BENEFIT = med.BENEFIT
WHERE ebm.BENEFITSTATUS_I = 1 -- Active Only
and ebm.BENEFIT in (select BENEFIT from BE020230 ben (nolock) where (ben.DSCRIPTN LIKE 'D/ UHC%' or ben.DSCRIPTN LIKE 'D/ Dental%'))
and ebm.empid_i = 1331
Group by ebm.EMPID_I
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000