Thanks everyone for their posts. I thought I would post my solution. The
basic steps are
1) Extract data to a temp table.
2) Use a cursor to loop through the data & number my contacts 1, 2, 3, 4,
etc.
3) Link to temp table & select contact 1, 2, etc.

=======================
extract data to temp table
=======================
CREATE TABLE
#t_tmpContactIds ( ContactId smallint identity(1,1),
ContactNumberTypeID int,
ContactNumber varchar(75),
extension varchar(8),
ContactNumberTimeID int,
ClientId int,
ClientIDOrder int)

insert into #t_tmpContactIds (ContactNumberTypeID, ContactNumber, Extension,
ContactNumberTimeID, ClientID)
select ContactNumberTypeID,
ContactNumber,
Extension,
ContactNumberTimeID,
ClientID
from t_contactnumber
where clientid in
(select clientid
from t_Client C
INNER JOIN t_ClientStatus CS
WITH (NOLOCK) ON CS.ClientStatusID = C.ClientStatusID
WHERE (C.IsPractice = 0)
AND ((C.OID IS NULL)
OR (C.OID = 0))
AND (CS.ClientStatusCode IN (0, 1)))
and ContactNumberTypeID not in (3,4,11,12)
=============================================
-- ORDER THE CONTACT NUMBERS
=============================================
DECLARE CUR_CONTACTNUMBER CURSOR
FOR SELECT ClientId, ContactID FROM #t_tmpContactIds order by ClientID,
ContactID

DECLARE @ContactID int
DECLARE @ClientId int
DECLARE @ClientIdlast int
DECLARE @ClientIDOrder int

DECLARE @count smallint
SELECT @count = 1

OPEN CUR_CONTACTNUMBER
FETCH NEXT FROM CUR_CONTACTNUMBER INTO @ClientID, @ContactID

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
if @ClientIDLast <> @ClientID select @Count = 1
UPDATE #t_tmpContactIds
SET ClientIDOrder = @Count
WHERE ContactID = @ContactID

select @ClientIDLast = @ClientID
END
FETCH NEXT FROM CUR_CONTACTNUMBER INTO @ClientID, @ContactID
SELECT @count = @count + 1
END

CLOSE CUR_CONTACTNUMBER
DEALLOCATE CUR_CONTACTNUMBER

select ...
LEFT OUTER JOIN #t_tmpContactIds TN1 WITH (NOLOCK) ON TN1.ClientID =
C.ClientID AND TN1.ClientIDOrder = 1
LEFT OUTER JOIN #t_tmpContactIds TN2 WITH (NOLOCK) ON TN2.ClientID =
C.ClientID AND TN2.ClientIDOrder = 2
LEFT OUTER JOIN #t_tmpContactIds TN3 WITH (NOLOCK) ON TN3.ClientID =
C.ClientID AND TN3.ClientIDOrder = 3
LEFT OUTER JOIN #t_tmpContactIds TN4 WITH (NOLOCK) ON TN4.ClientID =
C.ClientID AND TN4.ClientIDOrder = 4

=======================
link the data I want
=======================

MS Sql Server LazyDBA home page