I have 8 query's all have 5 unusual columns and some distinct columns, Based on the common columns, can I display a single result of 8?
Common Fields:
F.CHAPA AS CHAPA,
F.NOME AS NOME,
F.FILIAL AS FILIAL,
F.SECAO AS SECAO,
F.SITUACAO AS SITUACAO,
One possibility is to turn each query into a CTE and then join the result of the 8 queries. You must remove ORDER BY by entering the code for 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 doing 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 by indexing the CHAPA column in each temporary table, but you must evaluate locally if it makes up for it.
But the best way is to rewrite the queries, trying to group them into a single or as few queries as possible, in order to reduce the I / O in the tables.