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?

Pretty sure you need @ syntax. See http://stackoverflow.com/questions/8483936/tsql-for-xml-explicit — paparazzo, Jan 08 '15 at 17:45
That post doesn't seem to be related to my question at all. The issue is with the group by clause as I identified in the question. — SBB, Jan 08 '15 at 17:48
@SBB, use INNER JOIN ( select distinct intID from dbo.BS_Initiatives) AS int instead of INNER JOIN dbo.BS_Initiatives AS int — radar, Jan 08 '15 at 18:29
The issue isnt with the initiatives table though; its with ` FROM dbo.BS_ProjectReview_ProjectPresenters AS pres` — SBB, Jan 08 '15 at 18:32
@SBB, get distinct rows after joining those two tables , instead of doing group by — radar, Jan 08 '15 at 18:51

score 1 · Accepted Answer · answered Jan 09 '15 at 21:06

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');

TSQL Group By Issue causing duplicates

1 Answers1