Concatenate values from the same column

Navigation
3

How do I 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 |

In return, I need to always select the Form, and the Action concatenates only when Permit 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 feedback is wrong

    
asked by anonymous 28.06.2016 / 19:34

2 answers

2

Try to do 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
    
28.06.2016 / 20:37
0

I found a great example of this site link

Example: Customer Product Code 

1   Jorge   piso
1   Jorge   porta
1   Jorge   torneira

I need the result of this select to look 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;
    
14.06.2018 / 16:22
SQL query separated by space I need to register several products