--===== If the test table already exists, drop it
     IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
         DROP TABLE #mytable

--===== Create the test table with 
 CREATE TABLE #mytable 
        (
        GrpID INT,
        GrpName TEXT,
        PrvID INT,
        PrvName TEXT,
	 Spc TEXT,
        )

--===== Insert the test data into the test table
 INSERT INTO #mytable 
       (GrpID, GrpName, PrvID, PrvName, Spc)
SELECT '1234','Med Associates',NULL,NULL,'Mixed' UNION ALL
SELECT '1234','Med Associates','6875','Bill Ham','Cardiology' UNION ALL
SELECT '1234','Med Associates','3274','Sarah Jones','Cardiology' UNION ALL
SELECT '8975','Pulm Associates',NULL,NULL,'Mixed' UNION ALL
SELECT '5781','Babies Inc',NULL,NULL,'Pediatrics' UNION ALL
SELECT '6321','ABC Therapy',NULL,NULL,'Mixed' UNION ALL
SELECT '6321','ABC Therapy','4925','Tate Silverman','Physical Therapy' UNION ALL
SELECT '6321','ABC Therapy','3274','Sarah Jones','Occupational Therapy' 

SELECT * FROM #mytable

IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
    DROP TABLE #mytable

CREATE TABLE #mytable
    (
     ID INT IDENTITY(1,1) NOT NULL
    ,GrpID INT NULL
    ,GrpName VARCHAR(50) NULL
    ,PrvID INT NULL
    ,PrvName VARCHAR(50) NULL
    ,Spc VARCHAR(50) NULL
    ,PRIMARY KEY (ID)
    )

INSERT INTO #mytable (GrpID, GrpName, PrvID, PrvName, Spc)
SELECT '1234','Med Associates','','','Mixed' UNION ALL
SELECT '1234','Med Associates','6875','Bill Ham','Cardiology' UNION ALL
SELECT '1234','Med Associates','3274','Sarah Jones','Cardiology' UNION ALL
SELECT '8975','Pulm Associates','','','Mixed' UNION ALL
SELECT '5781','Babies Inc','','','Pediatrics' UNION ALL
SELECT '6321','ABC Therapy','','','Mixed' UNION ALL
SELECT '6321','ABC Therapy','4925','Tate Silverman','Physical Therapy' UNION ALL
SELECT '6321','ABC Therapy','3274','Sarah Jones','Occupational Therapy'


SELECT DISTINCT
    m.GrpID
   ,m.GrpName
   ,m.Spc
FROM
    #mytable AS m
INNER JOIN 
    (
    SELECT
         GrpID
        ,COUNT(DISTINCT Spc) AS SpcCount  
    FROM
        #mytable AS countspc
    WHERE
        ID > 0
    GROUP BY    
        GrpID
    ) Sub1
    ON m.GrpID = Sub1.GrpID
WHERE
    (Spc = 'Mixed' AND PrvID = 0 AND SpcCount = 1)
    OR (Spc <> 'Mixed' AND SpcCount = 2)
        
    

SELECT
	GrpID, GrpName, 	
	    ISNULL(NULLIF(MAX(CASE WHEN Spc <> 'Mixed' THEN Spc ELSE '' END), ''), 'Mixed') AS Spc
FROM #mytable
GROUP BY
	GrpID, GrpName
HAVING
	MAX(CASE WHEN Spc = 'Mixed' AND PrvName = '' THEN 1 ELSE 0 END) = 1 AND
	COUNT(DISTINCT CASE WHEN Spc <> 'Mixed' THEN Spc END) < 2
ORDER BY
    GrpID