Query query - SQL Server 2012

Question

Query query - SQL Server 2012

Navigation

#1 by (3 votes)

1

Good afternoon!

Well, I do not know if that's possible, but I wanted to know how I can get a one line record to stay on another line. I will illustrate. In the query below is coming out like this.

         Projeto                   Operadora
Telles - Bronze - Climario      Habilitação Cielo.
Telles - Bronze - Climario      Habilitação Rede.

I wanted to know if I would be able to stay as follows.

ProjDesc                             Operadora
Telles - Bronze - Climario      Habilitação Cielo e Habilitação Rede.

That is, join the network enable that is on the line below on the same line as the Sky enable. Below is the query used.

SELECT p.projdesc,
       CONCAT('Faltando ', tt.tartitulo)
  FROM projetos p
       INNER JOIN tarefa tt ON p.projid = tt.projid
       LEFT JOIN tipo c ON c.tipid = tt.tartipid
 WHERE tt.tarstatus <> 9
   AND p.projid = tt.projid
   AND c.tipdescricao LIKE 'Habilita%'
   AND tt.projid = p.projid
   AND tt.modid = 181
   AND tt.tartipid IN (867, 868, 869, 870,
                       871, 872, 873, 874,
                       875, 876, 877, 878,
                       879, 880, 881, 882,
                       883, 884, 885, 886,
                       887, 888, 889, 890,
                       891, 892, 893, 894,
                       895, 896, 897, 898,
                       899, 900, 901, 902,
                       903, 904, 905, 906,
                       907, 908, 909, 910,
                       911, 912, 913, 914,
                       915, 916, 917, 918,
                       919, 920, 921, 922,
                       923, 924, 925, 926,
                       927, 928, 929, 930,
                       931, 932, 933, 934,
                       1004, 1034)

sql sql-server query

asked by anonymous 04.08.2017 / 19:18

1 answer

How to cut a suffix of an expression in bash (egrep / sed / etc) Globalize - datetime does not work with pt-BR

score 3 · Accepted Answer

I created an example with a variable table based on the result of your first query:

DECLARE @tabela TABLE(id        INT IDENTITY,
                      projeto   VARCHAR(100),
                      operadora VARCHAR(100));

INSERT INTO @tabela
SELECT p.projdesc,
       tt.tartitulo
  FROM tarefa tt
       INNER JOIN projetos p ON p.projid = tt.projid
       LEFT JOIN tipo c ON c.tipid = tt.tartipid
 WHERE tt.modid = 181
   AND tt.tarstatus <> 9
   AND (tt.tartipid BETWEEN 867 AND 934
    OR tt.tartipid IN (1004, 1034))
   AND c.tipdescricao LIKE 'Habilita%';

SELECT r.projeto,
       STUFF((SELECT ', ' + t.operadora
                FROM @tabela t
               WHERE t.projeto = r.projeto
                 FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS operadoras
  FROM @tabela r
 GROUP BY projeto;

If you want to run the query in one go, use the expression WITH as follows:

WITH tabela (projeto, operadora) AS (
  SELECT p.projdesc,
         tt.tartitulo
    FROM tarefa tt
         INNER JOIN projetos p ON p.projid = tt.projid
         LEFT JOIN tipo c ON c.tipid = tt.tartipid
   WHERE tt.modid = 181
     AND tt.tarstatus <> 9
     AND (tt.tartipid BETWEEN 867 AND 934
      OR tt.tartipid IN (1004, 1034))
     AND c.tipdescricao LIKE 'Habilita%')

SELECT r.projeto,
       STUFF((SELECT ', ' + t.operadora
                FROM tabela t
               WHERE t.projeto = r.projeto
                 FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS operadoras
  FROM tabela r
 GROUP BY projeto;

Note / Enhancements:

I have simplified the constraint of the tartipid column by noting that the interval between 867 and 934 is fixed and the codes that should actually be declared are 1004 and 1034;
I have also improved the order of WHERE to use conditions that restrict most first and putting LIKE at the end, considering the cost of that statement;
I also removed two conditions that were redundant;
I set the order of the JOIN s to ensure that the most restrictive table is fetched first.

Reference: How to use GROUP BY to concatenate strings in SQL Server?