Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Table Convert Rows to Columns

Author  Topic 

wldodds
Starting Member

20 Posts

Posted - 2013-01-16 : 17:20:05
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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-16 : 22:14:49
[code]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[/code]
Go to Top of Page
   

- Advertisement -