I have two tables (tblReferrals and tblReferralChildren). I'm only going to include the pertinent fields in this question.
tblReferrals contains ReferralID (integer, not null, Identity, PK)
- ReferralChildID (integer, not null, Identity, PK)
- ReferralID (integer, FK to tblReferrals!ReferralID)
- ChildLastName - nvarchar(50)
- ChildFirstName - nvarchar(25)
There could be any number of children associated with a particular referral, but I'm only interested in the first 7. I would like to generate a query that returns the names like:
ReferralID Child1FullName Child2FullName Child3FullName ... Child7FullName
where the sort order of the children (1-7) is based on the sequence of ReferralChildID.