Doubt query - SQL Server 2012

Question

Good afternoon!

Guys, I don’t know if it’s possible, but I wanted to know how to get a record of a line in another line. I’ll 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 could stay this way.

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

i.e., join the network enabling that is in the row below in the same line of the Cielo enabling. 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)

Answer 1

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 perform the query at once, 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;

---

Observation/Improvements:

• I simplified the column restriction tartipid noting that the range between 867 and 934 is fixed and the codes that really should be declared are 1004 and 1034;
• I also improved the order of WHERE to use the conditions that restrict more first and putting the LIKE at the end, considering the cost of this instruction;
• I also removed two conditions that were redundant;
• I adjusted the order of JOINs to ensure that the most restricted table is searched first.

---

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