Join Names in a row

Question

Join Names in a row

Navigation

#1 by (0 votes)

2

Good afternoon, we use SQL SERVER 2008 as the official database server and 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 P.NAME field grouped according to the discipline, ie, if the course has more than one teacher, the name of both are returned on the same line

sql sql-server query sql-server-2008

asked by anonymous 05.04.2018 / 19:45

1 answer

Sub-menu decentralized - according to browser, I do not know where I'm going wrong PHP - Javascript

score 0 · Answer 1

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

I have simulated the minimum of your environment in SQLFiddle to be able to demonstrate, click on the link to view it.

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