Concatenate values from the same column

Question

How to concatenate the values of the same table

Table example

   | Usuario  |  Formulario  |    Acao    |  Permitir |
   |  Admin   |   Contrato   |  Imprimir  |     1     |
   |  Admin   |   Contrato   |  Consultar |     1     |
   |  Admin   |   Contrato   |   Editar   |     0     |
   |  Admin   |   Contrato   |   Excluir  |     1     |

Return

  | Formulario | Acao                         |
  |  Contrato  | Imprimir; Consultar; Excluir |

On return, I must always select the Form, and the Action concatenate only when Allow equals 1.

My code

SELECT 
  FormID,
  STUFF((
    SELECT ', ' +CAST(AcaoID AS VARCHAR(MAX)) 
    FROM PermissaoAcoesForms 
    WHERE (FormID = Results.FormID) AND FormID = 'CarteiraCompras' AND Permitir = 1 AND GrupoUsuario = 'Admin'
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS AcaoID
FROM PermissaoAcoesForms Results
GROUP BY FormID

But my return is bringing wrong

Answer 1

Try this, see if it solves your problem.

SELECT s.formulario,
       CONCAT(CASE WHEN s.a1 = NULL THEN "" ELSE CONCAT(s.a1, " ;") END, 
              CASE WHEN s.a2 = NULL THEN "" ELSE CONCAT(s.a2, " ;") END, 
              CASE WHEN s.a3 = NULL THEN "" ELSE CONCAT(s.a3, " ;") END, 
              CASE WHEN s.a4 = NULL THEN "" ELSE CONCAT(s.a4, " ;") END) AS acao
  FROM (SELECT r.formulario,
               MAX(a1) AS a1,
               MAX(a2) AS a2,
               MAX(a3) AS a3,
               MAX(a4) AS a4
          FROM (SELECT formulario,
                       CASE WHEN acao = "Imprimir" AND permitir = 1 THEN "Imprimir" ELSE NULL END a1
                       CASE WHEN acao = "Consultar" AND permitir = 1 THEN "Consultar" ELSE NULL END a2
                       CASE WHEN acao = "Editar" AND permitir = 1 THEN "Editar" ELSE NULL END a3
                       CASE WHEN acao = "Excluir" AND permitir = 1 THEN "Excluir" ELSE NULL END a4
                  FROM tabela) r
         GROUP BY (r.formulario)) s

Answer 2

I found a cool example of this site https://social.msdn.microsoft.com/Forums/sqlserver/pt-BR/6177bd7a-e2fc-46f4-9646-8fd1480cf14b/concatenar-valores-de-linhas-em-uma-coluna?forum=520

Example : Code Customer Product

1   Jorge   piso
1   Jorge   porta
1   Jorge   torneira

I need the result of this select to stay like this:

Codigo  Cliente Produto
1   Jorge   piso;porta;torneira

-- Concatenating

SELECT  CODIGO,
        CLIENTE,
    COALESCE(
        (SELECT CAST(PRODUTO AS VARCHAR(10)) + ';' AS [text()]
         FROM TABELA AS O
         WHERE O.CODIGO  = C.CODIGO
         and   O.CLIENTE = C.CLIENTE
         ORDER BY CODIGO
         FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS Produtos
FROM TABELA AS C
GROUP BY CODIGO,CLIENTE;