It really bothers me how many times repeat the call to the same table but I don't know how to do it better. Would appreciate any help
SELECT --top 20
matches.provnum
,matches.numOfCustIDs
,( select top 1 inMatches.custnmbr
from v_ourCMScustDetails inMatches with(nolock)
where inMatches.provnum = matches.provnum
AND inMatches.custclas <> 'ENTERAL'
AND inMatches.CUSTCLAS <> 'ENTERAL-MED'
order by inMatches.custIDlen
) as primaryCustID --pick ID with shortest length
,( select top 1 inMatches.userdef2
from v_ourCMScustDetails inMatches with(nolock)
where inMatches.provnum = matches.provnum
AND inMatches.custclas <> 'ENTERAL'
AND inMatches.CUSTCLAS <> 'ENTERAL-MED'
order by inMatches.custIDlen
) as primaryUserdef2 --pick corp with shortest length
,(
ltrim( SUBSTRING( (
select ', ' + inMatches.custnmbr as [text()]
from v_ourCMScustDetails inMatches with(nolock)
where inMatches.provnum = matches.provnum
and inMatches.custclas <> 'ENTERAL'
AND inMatches.CUSTCLAS <> 'ENTERAL-MED'
order by inMatches.provnum
for xml path ('')
) ,2 ,1000 ) )
) as custList --concatenate list of IDs
FROM(
select
matches.provnum
,count( matches.custnmbr ) as numOfCustIDs
from v_ourCMScustDetails matches with(nolock)
where matches.custclas <> 'ENTERAL'
AND matches.CUSTCLAS <> 'ENTERAL-MED'
GROUP BY matches.provnum
) matches