TSQL Group By Issue causing duplicates

Question

I have a query that I created which outputs projects as well as who is presenting the project. My issue is that I am trying to group the presenters to the project ID so it lists the presenters under the project ID and not include it multiple times.

Here is my query:

DECLARE @agendaID AS INT = '23';

SELECT 
   (SELECT att.empID,
           emp.firstName,
           emp.lastName,
           emp.ntid
    FROM   dbo.BS_ProjectReview_Attendees AS att
           LEFT OUTER JOIN
           dbo.EmployeeTable AS emp
           ON att.empID = emp.EmpID
    WHERE  att.agendaID = @agendaID
    FOR    XML PATH ('attendee'), TYPE, ELEMENTS, ROOT ('attendees')),
   (SELECT pres.intID,
           int.intTitle,
           (SELECT emp.firstName,
                   emp.lastName,
                   emp.ntid
            FROM   dbo.EmployeeTable AS emp
            WHERE  emp.EmpID = pres.empID
            FOR    XML PATH ('presenter'), TYPE, ELEMENTS, ROOT ('presenters'))
    FROM   dbo.BS_ProjectReview_ProjectPresenters AS pres
    INNER JOIN dbo.BS_Initiatives AS int
    ON pres.intID = int.intID
    WHERE  pres.agendaID = @agendaID
    GROUP BY int.intID <----- ISSUE IS HERE
    FOR    XML PATH ('project'), TYPE, ELEMENTS, ROOT ('projects'))
FOR    XML PATH ('data'), TYPE, ELEMENTS, ROOT ('root');

Here is my XML OUTPUT:

  <root>
<data>
<attendees>
  <attendee>
    <empID>1234</empID>
    <firstName>Mike</firstName>
    <lastName>Smith</lastName>
    <ntid>msmith</ntid>
  </attendee>
</attendees>
<projects>
  <project>
    <intID>202</intID>
    <intTitle>Infrastructure Expansion</intTitle>
    <presenters>
      <presenter>
        <firstName>Bob</firstName>
        <lastName>Jones</lastName>
        <ntid>bjones</ntid>
      </presenter>
    </presenters>
  </project>
  <project>
    <intID>202</intID>
    <intTitle>Infrastructure Expansion</intTitle>
    <presenters>
      <presenter>
        <firstName>User 1</firstName>
        <lastName>Last 1</lastName>
        <ntid>ulast1</ntid>
      </presenter>
    </presenters>
  </project>
</projects>
 </data>
</root>

Here is the desired output:

  <root>
<data>
<attendees>
  <attendee>
    <empID>1234</empID>
    <firstName>Mike</firstName>
    <lastName>Smith</lastName>
    <ntid>msmith</ntid>
  </attendee>
</attendees>
<projects>
  <project>
    <intID>202</intID>
    <intTitle>Infrastructure Expansion</intTitle>
    <presenters>
      <presenter>
        <firstName>Bob</firstName>
        <lastName>Jones</lastName>
        <ntid>bjones</ntid>
      </presenter>
       <presenter>
        <firstName>User 1</firstName>
        <lastName>Last 1</lastName>
        <ntid>ulast1</ntid>
      </presenter>
    </presenters>
  </project>
</projects>
 </data>
</root>

It should be listing both presenters under the same intID however its duplicating them.

I am getting an error with the group by clause as its not contained in an aggregate function and wants me to include pres.empID but then it would not make it unique as they are all different.

Any ideas?

Answer 1

This may be solved using this, however, since SQLFiddle is down ATM, no example.

DECLARE @agendaID AS INT = '23';

SELECT 
   (SELECT att.empID,
           emp.firstName,
           emp.lastName,
           emp.ntid
    FROM   dbo.BS_ProjectReview_Attendees AS att
           LEFT OUTER JOIN
           dbo.EmployeeTable AS emp
           ON att.empID = emp.EmpID
    WHERE  att.agendaID = @agendaID
    FOR    XML PATH ('attendee'), TYPE, ELEMENTS, ROOT ('attendees')),
   (SELECT pres.intID,
           int.intTitle,
           (SELECT emp.firstName,
                   emp.lastName,
                   emp.ntid
            FROM   dbo.EmployeeTable AS emp
                INNER JOIN dbo.BS_ProjectReview_ProjectPresenters AS pres ON emp.EmpID = pres.empID
            WHERE pres.intID = int.intID
            FOR    XML PATH ('presenter'), TYPE, ELEMENTS, ROOT ('presenters'))
    FROM   dbo.BS_Initiatives AS int
    WHERE  @agendaID IN ( SELECT pres.agendaID FROM dbo.BS_ProjectReview_ProjectPresenters AS pres WHERE pres.intID = int.intID)
    FOR    XML PATH ('project'), TYPE, ELEMENTS, ROOT ('projects'))
FOR    XML PATH ('data'), TYPE, ELEMENTS, ROOT ('root');