How to merge 8 query returns into one?

Question

I have 8 query’s all have 5 unusual columns and some distinct columns, Based on the common columns I can display a single result of the 8?

Common fields:

F.CHAPA     AS  CHAPA,
F.NOME      AS  NOME,
F.FILIAL    AS  FILIAL,
F.SECAO     AS  SECAO,
F.SITUACAO  AS  SITUACAO,

Answer 1

One possibility is to turn each query into a CTE and then join the result of the 8 queries. ORDER BY should be removed when entering the code of each query as CTE.

-- código #1  
with
Consulta1 as (
  ... insira aqui o código da primeira consulta
),

Consulta2 as (
  ... insira aqui o código da segunda consulta
),

  ...

Consulta8 as (
  ... insira aqui o código da oitava consulta
)

-- reúne o resultado das 8 consultas, agrupando-as pelo valor de CHAPA
SELECT C1.CHAPA, C1.NOME, C1.FILIAL, C1.SECAO, C1.SITUACAO,
       C2.colunas não comuns,
       ...
       C8.colunas não comuns
  from Consulta1 as C1
       inner join Consulta2 as C2 on C2.CHAPA = C1.CHAPA
       ...
       inner join Consulta8 as C8 on C8.CHAPA = C1.CHAPA
  where ...
  order by ... ;

---

Another possibility is to store the result of each query in temporary tables and after making the join.

-- código #2
-- apaga tabelas temporárias (possível erro em execução anterior)
IF Object_ID('tempDB..#Consulta1', 'U') is not null 
  DROP TABLE #Consulta1;
IF Object_ID('tempDB..#Consulta2', 'U') is not null 
  DROP TABLE #Consulta2;
...
IF Object_ID('tempDB..#Consulta8', 'U') is not null 
  DROP TABLE #Consulta8;
go

-- executa consulta #1
   ... inserir código da consulta 1, acrescentando
          into #Consulta1
       imediatamente antes da cláusula FROM
go

-- executa consulta #2
   ... inserir código da consulta 2, acrescentando
          into #Consulta2
       imediatamente antes da cláusula FROM
go

...


-- executa consulta #8
   ... inserir código da consulta 8, acrescentando
          into #Consulta8
       imediatamente antes da cláusula FROM
go

-- reúne o resultado das 8 tabelas temporárias
SELECT C1.CHAPA, C1.NOME, C1.FILIAL, C1.SECAO, C1.SITUACAO,
       C2.colunas não comuns,
       ...
       C8.colunas não comuns
  from #Consulta1 as C1
       inner join #Consulta2 as C2 on C2.CHAPA = C1.CHAPA
       ...
       inner join #Consulta8 as C8 on C8.CHAPA = C1.CHAPA
  where ...
  order by ... ;
go

-- apaga tabelas temporárias
IF Object_ID('tempDB..#Consulta1', 'U') is not null 
  DROP TABLE #Consulta1;
IF Object_ID('tempDB..#Consulta2', 'U') is not null 
  DROP TABLE #Consulta2;
...
IF Object_ID('tempDB..#Consulta8', 'U') is not null 
  DROP TABLE #Consulta8;
go

Code #2 can be optimized, with the creation of index by the PLATE column in each temporary table, but it is necessary to evaluate locally if it compensates.

---

But the best way is to rewrite the queries, trying to group them into a single or as few queries as possible, so as to reduce the I/O in the tables.