tsql - non duplicate multiple SQL records I can't get rid of -

tsql - non duplicate multiple SQL records I can't get rid of -

i'm trying generate study show users restricted facilities. base of operations code i'm using is:

`select distinct ppp.deptid, mis.userid, mis.[name], case when mis.providerid null 'no' else 'yes' end 'isprov', case when af.facilityid = 'psy' 'restricted' else 'not restricted' end 'signonfacility', case when fr.facilitiesrestrictedid = 'psy' 'restricted' else 'not restricted' end 'facilitytorestrict', mis.active dmisusers mis left bring together dmisuserrestrictedadmmrifac fr on mis.userid = fr.userid , mis.sourceid = fr.sourceid left bring together dmisuseradmmrifacility af on af.userid = mis.userid inner bring together ppemployees pe on mis.sourceid = pe.sourceid , mis.userid = pe.userid inner bring together pppayrollposition ppp on ppp.employeeid = pe.employeeid , pe.sourceid = ppp.sourceid mis.sourceid = 'stp' , mis.active = 'y'`

which produces next result set bringing in 1 row each 'facility' column.

| deptid | userid |isprov| signonfacility | facilitytorestrict | active| |01.9030 | ppaul | no | not restricted | not restricted | y | |01.9030 | ppaul | no | not restricted | restricted | y |

what i'm trying single row :

| deptid | userid |isprov| signonfacility | facilitytorestrict | active| |01.9030 | ppaul | no | not restricted | restricted | y |

i've tried cte's, pivots using row_number() around aggregation, , several other methods. help appreciated.

i'm pretty sure want group by. here guess logic is:

select ppp.deptid, mis.userid, mis.[name], coalesce(max(case when mis.providerid not null 'yes' end), 'no') isprov, coalesce(max(case when af.facilityid <> 'psy' 'not restricted' end), 'restricted') signonfacility, coalesce(max(case when fr.facilitiesrestrictedid <> 'psy' 'not restricted' end) facilitytorestrict, mis.active . . . grouping ppp.deptid, mis.userid, mis.[name], mis_active;

sql tsql pivot ssms