Olly, a question before I work this code.

First, you put in 0-length strings, '', instead of NULLS where teh data is missing for PRVID/PrvName. Are these NULL, or actually blanks?

Also, anyone else trying to work the problem, the script provided is a little non-functional due to special quotes usage and no identity fields. Use the following:

--===== 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