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