Sql - Concatenate values if field is equal

Asked

Viewed 94 times

1

I have the following select:

SELECT c.codigo
      ,c.situacao
      ,c.fk_aluno
  FROM       tb_cad   AS c
  INNER JOIN tb_aluno AS a ON c.fk_aluno = a.id 
  order by c.fk_aluno

I need when c.fk_aluno is equal, agglutinate all c.codigo in a single line.

Select return example above:

17162   A   74
17045   A   75
17177   A   75
17175   A   75
17174   A   75
17182   I   75

I need you to c.fk_aluno is repeated, join in a row and concatenate

17162   A   74
17045 A, 17177 A,17175 A, 17174 A, 17182 I  75

Using SQL Server 2012

sql sql-server sql-server-2012

  • Let us know your database! So we can help you in a better way...

  • sql server 2012

  • Beauty, when so edit your question and put that information there, you can do by link

  • 1

    Possible duplicate of Concatenate results

  • But do you want everything in the same column? Or the columns codigo and situacao is in one column and the fk_aluno in a separate?

  • code and situation is in one column and fk_pupil in another separate

  • If the answer below solves your problem, give an UP and mark it as right! (I give this statement here because @user141448 edited my reply to inform that solved the problem).

Show 2 more comments

1 answer

0

Given the issue and the comments, I think this will solve your problem:

SELECT      STUFF(
                    (
                        SELECT  ', ' + codigo + '   ' + situacao
                        FROM    tb_cad
                        WHERE   (fk_aluno = C.fk_aluno) 
                        FOR     XML PATH(''), TYPE
                    ).value('(./text())[1]','VARCHAR(MAX)')
                , 1, 2, '') AS CodigoSituacao
        ,   C.fk_aluno
FROM        tb_cad   C
INNER JOIN  tb_aluno A ON C.fk_aluno = A.id 
GROUP BY    C.fk_aluno
ORDER BY    C.fk_aluno

Another way will be to use the suggestion that the @Sorak gave in to link in comment (without the use of STUFF):

SELECT      SUBSTRING(
                        (
                            SELECT  ', ' + codigo + '   ' + situacao
                            FROM    tb_cad
                            WHERE   (fk_aluno = C.fk_aluno) 
                            FOR     XML PATH('')
                        ), 2, 8000) AS CodigoSituacao
        ,   C.fk_aluno
FROM        tb_cad   C
INNER JOIN  tb_aluno A ON C.fk_aluno = A.id 
GROUP BY    C.fk_aluno
ORDER BY    C.fk_aluno

Browser other questions tagged sql sql-server sql-server-2012

You are not signed in. Login or sign up in order to post.