Merge Names into a line

Question

Good afternoon we use SQL SERVER 2008 as the official database server and we have the following query :

SELECT P.NOME              AS [PROFESSOR],
       STIPOCURSO.NOME       AS [NÍVEL DE ENSINO],
       SPL.CODPERLET         AS [ANO],
       STURMA.NOME           AS [TURMA],
       STURNO.NOME           AS [TURNO],
       SD.NOME               AS [DISCIPLINA],
       SPT.AULASSEMANAISPROF AS [AULAS SEMANAIS]
FROM   SPROFESSOR SP (NOLOCK)
       INNER JOIN PPESSOA P
         ON P.CODIGO = SP.CODPESSOA
       INNER JOIN SPROFESSORTURMA SPT (NOLOCK)
         ON SP.CODCOLIGADA = SPT.CODCOLIGADA
            AND SP.CODPROF = SPT.CODPROF
       INNER JOIN STURMADISC ST (NOLOCK)
         ON SPT.CODCOLIGADA = ST.CODCOLIGADA
            AND SPT.IDTURMADISC = ST.IDTURMADISC
       INNER JOIN SPLETIVO SPL (NOLOCK)
         ON ST.IDPERLET = SPL.IDPERLET
            AND ST.CODCOLIGADA = SPL.CODCOLIGADA
       INNER JOIN STURNO (NOLOCK)
         ON ST.CODCOLIGADA = STURNO.CODCOLIGADA
            AND ST.CODTURNO = STURNO.CODTURNO
       INNER JOIN SDISCIPLINA SD (NOLOCK)
         ON ST.CODCOLIGADA = SD.CODCOLIGADA
            AND ST.CODDISC = SD.CODDISC
       INNER JOIN STIPOCURSO (NOLOCK)
         ON SD.CODTIPOCURSO = STIPOCURSO.CODTIPOCURSO
            AND SD.CODCOLIGADA = STIPOCURSO.CODCOLIGADA
       INNER JOIN STURMA (NOLOCK)
         ON ST.CODTURMA = STURMA.CODTURMA
            AND SPL.IDPERLET = STURMA.IDPERLET
WHERE  STURMA.CODCOLIGADA = :CODCOLIGADA1
       AND STURMA.IDHABILITACAOFILIAL = :IDHABILITACAOFILIAL1
       AND STURMA.IDPERLET = :IDPERLET1
       AND STURMA.CODTURMA = :CODTURMA1
GROUP  BY P.NOME,
          STIPOCURSO.NOME,
          SPL.CODPERLET,
          STURMA.NOME,
          STURNO.NOME,
          SPT.AULASSEMANAISPROF,
          SD.NOME 

I would like to know how I can make the field P.NAME be grouped according to the discipline, that is, regardless if the discipline has more than one teacher the name of both are returned in the same line

Answer 1

I made a minimum example, only using the tables necessary to display it to the teachers of the discipline, in this example I make a subselect and concatenate the results in a single line.

I simulated the least of your Sqlfiddle to demonstrate, click on the link to view.

Sqlfiddle - Online example:

SELECT 
   STUFF(
      (SELECT ',' + CAST(PPESSOA.NOME AS VARCHAR(MAX))
      FROM SPROFESSORTURMA AS SPT2
      JOIN SPROFESSOR
        ON SPT2.CODPROF = SPROFESSOR.CODPROF
      JOIN PPESSOA
        ON PPESSOA.CODPESSOA = SPROFESSOR.CODPESSOA
      WHERE SPT2.IDTURMADISC = SPT.IDTURMADISC
      FOR XML PATH('')
    ),1,1,'')    AS [PROFESSORES],
   SD.NOME       AS [DISCIPLINA]
FROM SPROFESSORTURMA SPT
LEFT JOIN STURMADISC ST
   ON SPT.IDTURMADISC = ST.IDTURMADISC
LEFT JOIN SDISCIPLINA SD
   ON ST.CODDISC = SD.CODDISC
LEFT JOIN STURMA
   ON ST.CODTURMA = STURMA.CODTURMA
GROUP BY STURMA.NOME
  , SD.NOME
  , SPT.IDTURMADISC