>OK, sorry, I neglected to mention something.....in my example I created the min # of rows to keep the example simple. In a PIVOT, the "source" is usually a table with the minimal # of columns for the PIVOT. Any additional columns in this instance are going to generate the extra results. Sorry, I should have stuck with the structure you were using.
>
>At any rate, as Naomi said, if you PIVOT from a subqery that has the minimum columns (name, phone type, phone)....or create a temp table or common table expression with just those columns, and then PIVOT off of that, that should work.
Thanks all. This did it:
SELECT ContactID, [1] AS [Home], [2] AS Office, [3] AS Fax, [4] AS Cell
FROM (SELECT ContactID, PhoneNum, PhoneTypeID
FROM Phones
WHERE ContactID = 4010) src
PIVOT (MIN(PhoneNum) for PhoneTypeID in ([1],[2],[3],[4])) pvt
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people