• Here's what I came up with:

    DECLARE @Cmd VARCHAR(MAX);

    SELECT @Cmd = 'select ' + STUFF((SELECT ',(select [name] '

    + ' from RELATIONSHIP where Record_Num = ' + CAST(Record_Num AS VARCHAR(10)) + ') as Child' + CAST(ROW_NUMBER() OVER (PARTITION BY People_Code_ID ORDER BY Record_Num) AS VARCHAR(10))

    FROM RELATIONSHIP

    ORDER BY Record_Num

    FOR XML PATH(''), TYPE).value('./text()[1]','varchar(max)'), 1,1,'');

    EXEC (@Cmd);

    Does that get you started?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon