如何在PHP中合并具有相同ID的数据？

Question

My select returns values like this:

It can merge the two lines into one? If so, it is possible in php, or Tsql?
Something like:

This is my select:

           SELECT TRM_ID_TURMA
                 , TRM_HORA_INICIO
                 , TRM_HORA_FIM
                 , TRM_DIA
                 , TRM_VAGAS
                 , MGN_NOME
                 , PROF.USU_NOME AS PROFESSOR
                 , ALU_MATRICULA
                 , USU.USU_NOME NOMEALUNO
              FROM [MGB].[dbo].[TB_TURMA]
             INNER JOIN TB_USUARIO PROF ON PROF.USU_ID_USUARIO = TRM_ID_PROFESSOR
             INNER JOIN TB_NIVEL_TURMA ON NTR_ID_NIVEL_TURMA = TRM_ID_NIVEL
             INNER JOIN TB_MGB_NIVEL ON MGN_ID_MGB_NIVEL = NTR_ID_MGB_NIVEL
             INNER JOIN TB_LOCAL ON LOC_ID_LOCAL = TRM_ID_LOCAL
              LEFT JOIN TB_TURMA_ALUNO ON TAL_ID_TURMA = TRM_ID_TURMA
              LEFT JOIN TB_ALUNO ON ALU_ID_ALUNO = TAL_ID_ALUNO
              LEFT JOIN TB_USUARIO USU ON USU.USU_ID_USUARIO = ALU_ID_USUARIO
             WHERE TRM_ID_ACADEMIA = 27
               AND TRM_STATUS >= 0
               AND (TAL_STATUS IS NULL OR TAL_STATUS >= 0)
             ORDER BY TRM_HORA_INICIO,  TRM_HORA_FIM, TRM_DIA, ALU_MATRICULA, NOMEALUNO

Answer 1

Rephrased, I think you say, When TRM_ID_TURMA is the same, pick one row, and join (with comma's) the contents of ALU_MATRICULA and NOMEALUNO.

Is that right?

If so, I think you should solve it in PHP, because that is much easier than in SQL (whichever dialect you happen to use is a mystery to me, I see mysql, sql, oracle and tsql).

Suppose you have run your query, and stored your result in an associative resultset, an array, then try something like this:

$RS = ......your query that results in an array.......
/* 
$RS looks like:
array(
 [0] => array("TRM_ID_TURMA"=>48, "TRM_HORA_INICIO" => "10:00:00 000000", ..etc..),
 [1] => array("TRM_ID_TURMA"=>49, "TRM_HORA_INICIO" => "10:00:00 000000", ..etc..)
 [2] => etc etc
)
*/

$tmp = array();
foreach ($RS as $one){
  // $tmp used TRM_ID_TURMA as key, and row as content
  if (isset($tmp[$one["TRM_ID_TURMA"]])){
    // TRM_ID_TURMA already used, add to the fields ALU_MATRICULA and NOMEALUNO.
    $tmp[$one["TRM_ID_TURMA"]]["ALU_MATRICULA"] .= ", " . $one["ALU_MATRICULA"];
    $tmp[$one["TRM_ID_TURMA"]]["NOMEALUNO"] .= ", " . $one["NOMEALUNO"];
  } else {
    $tmp[$one["TRM_ID_TURMA"]] = $one;
  }
}

And your desired result is now in $tmp (with TRM_ID_TURMA as keys)