text: 
  
  --Get the best values from a group of records and aggregate
  
  
   WITH SAMPLE AS ( SELECT DISTINCT   [PROVIDER_RENDERING_NPI]
  		  ,[PROVIDER_BILLING_NPI]
  		  ,[GLID],[SVC_LOC_ORGNAME],[Scrb_Unit] FROM xyz] A)
   , A AS ( 
   SELECT  DISTINCT 
          [PROVIDER_RENDERING_NPI]
        , [PROVIDER_BILLING_NPI]
        , [GLID]
  	  ,	FIRST_VALUE ([SVC_LOC_ORGNAME])  OVER (PARTITION BY [PROVIDER_RENDERING_NPI]      ,[PROVIDER_BILLING_NPI]      ,[GLID]  ORDER BY LEN(RTRIM([SVC_LOC_ORGNAME])) DESC) [Best_SVC_LOC_ORGNAME]
        , FIRST_VALUE ([Scrb_Unit])  OVER (PARTITION BY [PROVIDER_RENDERING_NPI]      ,[PROVIDER_BILLING_NPI]      ,[GLID]  ORDER BY LEN(RTRIM([Scrb_Unit])) DESC) [Best_Scrb_Unit]
    FROM Sample)
    SELECT  * FROM A 