How to flatten rows into columns and specify column names
<p>I have seen a lot of the answers about pivoting and other solutions for transforming a 1:M relationship from many rows to many columns, but I have not seen one that addresses my specific scenario. I'm using SQL Server 2014.</p>

<p>I have two tables: Attendee and AttendeeChild.  Here are the relevant columns in each:</p>

<h2>Attendee</h2>

<p>Id</p>

<h2>AttendeeChild</h2>

<p>Id<br>
AttendeeId<br>
Age<br>
Name<br>
Gender  </p>

<p>An Attendee can have any number of "child" rows. For each attendee, I want to flatten the AttendeeChild rows into columns so the the output looks like this for AttendeeId = 1;</p>

<pre><code>AttendeeId Child1Age Child1Name Child1Gender Child2Age Child2Name Child2Gender
---------- --------- ---------- ------------ --------- ---------- ------------
1          10        Sam        Boy          9         Sally      Girl        
</code></pre>

<p>The set of child related attributes should continue to expand the number of columns for each AttendeeChild row and the column header should keep the pattern "Child{#}{Attribute}" where {#} is an incrementing counter of the next child row from AttendeeChild and {Attribute} is either "Name", "Age" or "Gender".</p>

<p>I hope this is enough information to convey the issue.</p>