Need help with a join in SQL and to not get duplicate record and case statement in this query

Transact-SQL
1

Here's my query and OUTPUT at the end. I need help to get this query not duplicate. thank you very much
SELECT SUB_EMPLOYEE,
(CASE WHEN SUB_PLAN_CODE='LSFS' THEN 1 ELSE NULL END) AS LSFS,
SUM(CASE WHEN SUB_PLAN_CODE='LSFS' THEN SUB_EMP_COST ELSE NULL END) AS LSFS_COST,

   (CASE WHEN SUB_PLAN_CODE='FSAM' THEN 1 ELSE NULL END) AS FSAM,
   SUM(CASE WHEN SUB_PLAN_CODE='FSAM' THEN SUB_EMP_COST ELSE NULL END) AS FSAM_COST,

   (CASE WHEN SUB_PLAN_CODE='HSAV' THEN 1 ELSE NULL END) AS HSAV,
   SUM(CASE WHEN SUB_PLAN_CODE='HSAV' THEN SUB_EMP_COST ELSE NULL END) AS HSAV_COST,

   (CASE WHEN SUB_PLAN_CODE='MED1' THEN 1 ELSE NULL END) AS MED1,
   SUM(CASE WHEN SUB_PLAN_CODE='MED1' THEN SUB_EMP_COST ELSE NULL END) AS MED1_COST,

   (CASE WHEN SUB_PLAN_CODE='MED4' THEN 1 ELSE NULL END) AS MED4,
   SUM(CASE WHEN SUB_PLAN_CODE='MED4' THEN SUB_EMP_COST ELSE NULL END) AS MED4_COST

FROM
(
SELECT BNBEN.EMPLOYEE AS SUB_EMPLOYEE,
BNBEN.PLAN_CODE AS SUB_PLAN_CODE,
BNBEN.EMP_COST AS SUB_EMP_COST
FROM BNBEN
WHERE ((BNBEN.EMPLOYEE IN (1666, 1712) AND BNBEN.PLAN_TYPE='RS')
OR (BNBEN.EMPLOYEE IN (1666, 1712) AND BNBEN.PLAN_CODE IN ('MEDW','MED1','MED4')))
) SUB
GROUP BY SUB_EMPLOYEE,SUB_PLAN_CODE
ORDER BY SUB_EMPLOYEE

OUTPUT FROM THIS QUERY

Sub_Emp Lsfs Lsfs_Cost Fsam Fsam_Cost Hsav Hsav_Cost Med1 Med1_Cost Med4 Med4_Cost
1666 NULL NULL 1 100.00 NULL NULL NULL NULL NULL NULL
1666 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1666 NULL NULL NULL NULL 1 230.77 NULL NULL NULL NULL
1666 NULL NULL NULL NULL NULL NULL NULL NULL 1 57.87
1666 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1712 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1712 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1712 NULL NULL NULL NULL 1 269.23 NULL NULL NULL NULL
1712 1 101.92 NULL NULL NULL NULL NULL NULL NULL NULL
1712 NULL NULL NULL NULL NULL NULL NULL NULL 1 22.80

Here's the OUPT I WANT to have

sub_emp Lsfs Lsfs_Cost Fsam Fsam_Cost Hsav Hsav_Cost Med1 Med1_Cost Med4 Med4_Cost
1666 NULL NULL 1 100.00 1 230.77 NULL NULL 1 57.87
1712 1 101.92 NULL NULL 1 269.23 NULL NULL 1 22.80

2 
SELECT SUB_EMPLOYEE,
    MAX(CASE WHEN SUB_PLAN_CODE='LSFS' THEN 1 ELSE NULL END) AS LSFS,
    SUM(CASE WHEN SUB_PLAN_CODE='LSFS' THEN SUB_EMP_COST ELSE 0 END) AS LSFS_COST,
    MAX(CASE WHEN SUB_PLAN_CODE='FSAM' THEN 1 ELSE NULL END) AS FSAM,
    SUM(CASE WHEN SUB_PLAN_CODE='FSAM' THEN SUB_EMP_COST ELSE 0 END) AS FSAM_COST,
    MAX(CASE WHEN SUB_PLAN_CODE='HSAV' THEN 1 ELSE NULL END) AS HSAV,
    SUM(CASE WHEN SUB_PLAN_CODE='HSAV' THEN SUB_EMP_COST ELSE 0 END) AS HSAV_COST,
    MAX(CASE WHEN SUB_PLAN_CODE='MED1' THEN 1 ELSE NULL END) AS MED1,
    SUM(CASE WHEN SUB_PLAN_CODE='MED1' THEN SUB_EMP_COST ELSE 0 END) AS MED1_COST,
    MAX(CASE WHEN SUB_PLAN_CODE='MED4' THEN 1 ELSE NULL END) AS MED4,
    SUM(CASE WHEN SUB_PLAN_CODE='MED4' THEN SUB_EMP_COST ELSE 0 END) AS MED4_COST
FROM
(
SELECT BNBEN.EMPLOYEE AS SUB_EMPLOYEE,
BNBEN.PLAN_CODE AS SUB_PLAN_CODE,
BNBEN.EMP_COST AS SUB_EMP_COST
FROM BNBEN
WHERE ((BNBEN.EMPLOYEE IN (1666, 1712) AND BNBEN.PLAN_TYPE='RS')
OR (BNBEN.EMPLOYEE IN (1666, 1712) AND BNBEN.PLAN_CODE IN ('MEDW','MED1','MED4')))
) SUB
GROUP BY SUB_EMPLOYEE --<-- remove SUB_PLAN_CODE from GROUP BY
ORDER BY SUB_EMPLOYEE
3

When I removed the "SUB_PLAN_CODE", I got an error following
Msg 8120, Level 16, State 1, Line 2
Column 'SUB.SUB_PLAN_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

4 
select employee
      ,min(case when plan_code='LSFS' then 1        else null end) as LSFS
      ,sum(case when plan_code='LSFS' then emp_cost else null end) as LSFS_COST
      ,min(case when plan_code='FSAM' then 1        else null end) as FSAM
      ,sum(case when plan_code='FSAM' then emp_cost else null end) as FSAM_COST
      ,min(case when plan_code='HSAV' then 1        else null end) as HSAV
      ,sum(case when plan_code='HSAV' then emp_cost else null end) as HSAV_COST
      ,min(case when plan_code='MED1' then 1        else null end) as MED1
      ,sum(case when plan_code='MED1' then emp_cost else null end) as MED1_COST
      ,min(case when plan_code='MED2' then 1        else null end) as MED2
      ,sum(case when plan_code='MED2' then emp_cost else null end) as MED2_COST
  from bnben
 where employee in (1666,1712)
   and (plan_type='RS'
    or  plan_code in ('MEDW','MED1','MED4')
 group by employee
 order by employee
;
5

SUB_PLAN_CODE is not in my query with the GROUP BY at all. You can't put in the query itself, you have to rely on the flag columns: LSFS, FSAM, etc..

You need to copy my entire query, not just change the GROUP BY. I just commented that so you'd see that it was a necessary part of changing the query.

6

it worked! thank you very much Bitsmed!!