Attempting to concatenate multiple rows.. unexpected results

Issue:

I'm trying to return a list of contacts with a column containing all the parent item names concatenated together. Please don't bother telling me this violates 1st normal form, I already know. I've been trying to get this working for so long that it is probably some stupid mistake, but I need some help at this point.

Query:

SELECT  C.ID, C.SequenceNumber, C.ContactType, C.GUID, C.ContactCategory, MAP.Parents, C.LastName, C.FirstName, C.CompanyName, C.Title, C.Email, C.Phone, C.MobilePhone, C.Fax, C.Comments, C.StandardConfirmation, C.Active FROM
(
SELECT ContactTypeMapping.Contact_GUID, STUFF( ( SELECT ','+ [NAME]
                        FROM ContactParents a
                        WHERE a.GUID =b.GUID
                        FOR XML PATH('')),1 ,1, '') Parents

FROM ContactParents b, ContactTypeMapping
WHERE ContactTypeMapping.Parent_GUID=b.GUID 
) MAP
INNER JOIN
(
SELECT Contact.ID, Contact.GUID, Contact.SequenceNumber, 
       Contact.ContactType, Contact.ContactCategory, Contact.LastName, Contact.FirstName, 
       Contact.CompanyName, Contact.Title, Contact.Email, Contact.Phone, 
       Contact.MobilePhone, Contact.Fax, Contact.Comments, Contact.StandardConfirmation, Contact.Active
      FROM Contact
       )C
     ON (MAP.Contact_GUID=C.GUID)

Current results:

ID   SequenceNumber  ContactType GUID                                    ContactCategory Parents         LastName    FirstName   CompanyName     Title       Email                       Phone           MobilePhone Fax             Comments    StandardConfirmation    Active
15   4              2           95A566D0-DB83-4853-9CB7-E6CF3B1FF814    0               AParent         Beard       Kirk        NULL            Business    [email protected]    913-906-3333    NULL        (913) 906-3434  NULL        0                       1
15   4              2           95A566D0-DB83-4853-9CB7-E6CF3B1FF814    0               AnotherParent   Beard       Kirk        NULL            Business    [email protected]    913-906-3333    NULL        (913) 906-3434  NULL        0                       1

Desired results:

15  4   2   95A566D0-DB83-4853-9CB7-E6CF3B1FF814    0   AParent,AnotherParent Beard   Kirk    NULL    Business    [email protected]    913-906-3333    NULL    (913) 906-3434  NULL    0   1

Data:

ContactParents Table:

GUID                                    NAME            TYPE
C40A6F7E-F760-48D6-8BAF-E55EC7DC900D    AParent         Place
A651A0A3-5A50-45F1-AB4B-2B7FDCE9734C    AnotherParent   Place

ContactTypeMapping Table:

Contact_GUID                            Parent_GUID                        ParentTable
95A566D0-DB83-4853-9CB7-E6CF3B1FF814    C40A6F7E-F760-48D6-8BAF-E55EC7DC900D   Place
95A566D0-DB83-4853-9CB7-E6CF3B1FF814    A651A0A3-5A50-45F1-AB4B-2B7FDCE9734C   Place

Contact Table:

ID  GUID                                   SequenceNumber   ContactType LastName    FirstName   Title       Email                       Phone          MobilePhone  Fax            Comments Active  ContactCategory CompanyName StandardConfirmation
15  95A566D0-DB83-4853-9CB7-E6CF3B1FF814    4               2           Beard       Kirk        Business    [email protected]    913-906-3333    NULL        (913) 906-3434  NULL     1        0              NULL        0

Solution 1:

;WITH cp AS -- contact parents - initial join
(
    SELECT cp.NAME, ctm.Parent_GUID
      FROM ContactParents AS cp
      INNER JOIN ContactTypeMapping AS ctm
      ON cp.GUID = ctm.Contact_GUID
),
cm AS -- contact mapping with concatenated values
(
    SELECT Parent_GUID, Parents = (
            SELECT STUFF ((SELECT ','+ [NAME] FROM cp AS cp2 
              WHERE cp2.Parent_GUID = cp.Parent_GUID 
              FOR XML PATH(''),
              TYPE).value(N'./text()[1]',N'nvarchar(max)'),1 ,1, '')
          )
      FROM cp GROUP BY Parent_GUID
)
SELECT c.[GUID], cm.Parents --, other columns from c 
  FROM Contact AS c
  INNER JOIN cm
  ON c.[GUID] = cm.Parent_GUID;

To demonstrate how I verified that this query returns the right results, here are the table variables I created locally, how I populated them, and a slightly different query that references the table variables:

DECLARE @ContactParents TABLE
(
    [GUID] UNIQUEIDENTIFIER,
    NAME VARCHAR(32),
    [TYPE] VARCHAR(32)
);

INSERT @ContactParents VALUES
('C40A6F7E-F760-48D6-8BAF-E55EC7DC900D','AParent','Place'),
('A651A0A3-5A50-45F1-AB4B-2B7FDCE9734C','AnotherParent','Place');

DECLARE @ContactTypeMapping TABLE
(
    Contact_GUID UNIQUEIDENTIFIER,
    Parent_GUID UNIQUEIDENTIFIER,
    ParentTable VARCHAR(32)
);

INSERT @ContactTypeMapping VALUES
('A651A0A3-5A50-45F1-AB4B-2B7FDCE9734C','95A566D0-DB83-4853-9CB7-E6CF3B1FF814','Place'),
('C40A6F7E-F760-48D6-8BAF-E55EC7DC900D','95A566D0-DB83-4853-9CB7-E6CF3B1FF814','Place');

DECLARE @Contact TABLE
(
    ID INT,
    [GUID] UNIQUEIDENTIFIER,
    LastName VARCHAR(32),
    FirstName VARCHAR(32)
    --, other columns...
);

INSERT @Contact VALUES
(15, '95A566D0-DB83-4853-9CB7-E6CF3B1FF814', 'Beard', 'Kirk');

;WITH cp AS
(
    SELECT cp.NAME, ctm.Parent_GUID
      FROM @ContactParents AS cp
      INNER JOIN @ContactTypeMapping AS ctm
      ON cp.GUID = ctm.Contact_GUID
),
cm AS
(
    SELECT Parent_GUID, Parents = (
      SELECT STUFF ((SELECT ','+ [NAME] FROM cp AS cp2 
        WHERE cp2.Parent_GUID = cp.Parent_GUID 
        FOR XML PATH('')),1 ,1, '')
      )
    FROM cp
    GROUP BY Parent_GUID
)
SELECT c.[GUID], cm.Parents --, other columns from c
  FROM @Contact AS c
  INNER JOIN cm
  ON c.[GUID] = cm.Parent_GUID;

Results

GUID                                    Parents
95A566D0-DB83-4853-9CB7-E6CF3B1FF814    AParent,AnotherParent