Consulta com join

[Victor Noda]

Boa noite, pessoal!

Imaginemos que exista uma tabela Teste com os seguintes dados:

Mes  Nome_Regional Nome_Convenio Qtde_Atendimentos                                     
---- ------------- ------------- --------------------
fev  Leste         Banco         300.0
fev  Norte         Escola        60.0
fev  Sul           Escola        480.0
fev  Sul           Hospital      80.0
jan  Leste         Banco         560.0
jan  Sul           Escola        350.0
mar  Norte         Escola        80.0
mar  Sul           Escola        370.0
mar  Sul           Hospital      120.0

Há meses em que podem ter novas regiões ou convênios, da mesma forma que podem ser excluídos ou estar em branco em um outro mês. Como faço uma consulta para ter resultado com estrutura semelhante a abaixo?

Nome_Regional Nome_Convenio Total_Jan  Total_Fev  Total_Mar                                             
------------- ------------- ---------- ---------- ---------- 
Leste         Banco         560.0      300.0      NULL
Norte         Escola        NULL       60.0       NULL
Norte         Escola        NULL       NULL       80.0
Sul           Escola        350.0      480.0      370.0
Sul           Hospital      NULL       80.0       NULL
Sul           Hospital      NULL       NULL       120.0

Eu até criei uma consulta funcional mas ela tá muito pesada! (na vida real, tenho que trabalhar com uma tabela com estrutura parecida, com quase 2.000.000 de registros e a consulta tem que verificar 6 meses) Segue script para criar e alimentar a tabela Teste, além do select louco que utilizei.

-- criação da tabela Teste
CREATE TABLE Teste (Mes Varchar(3), Nome_Regional Varchar(10), Nome_Convenio Varchar(10), Qtde_Atendimentos Float)

-- inclusão de dados em Teste
INSERT INTO Teste
SELECT 'jan' AS Mes, 'Sul' AS Nome_Regional, 'Escola' AS Nome_Convenio, '350' AS Qtde_Atendimentos
UNION
SELECT 'fev' AS Mes, 'Sul' AS Nome_Regional, 'Escola' AS Nome_Convenio, '480' AS Qtde_Atendimentos
UNION
SELECT 'mar' AS Mes, 'Sul' AS Nome_Regional, 'Escola' AS Nome_Convenio, '370' AS Qtde_Atendimentos
UNION
SELECT 'fev' AS Mes, 'Sul' AS Nome_Regional, 'Hospital' AS Nome_Convenio, '80' AS Qtde_Atendimentos
UNION
SELECT 'mar' AS Mes, 'Sul' AS Nome_Regional, 'Hospital' AS Nome_Convenio, '120' AS Qtde_Atendimentos
UNION
SELECT 'fev' AS Mes, 'Norte' AS Nome_Regional, 'Escola' AS Nome_Convenio, '60' AS Qtde_Atendimentos
UNION
SELECT 'mar' AS Mes, 'Norte' AS Nome_Regional, 'Escola' AS Nome_Convenio, '80' AS Qtde_Atendimentos
UNION
SELECT 'jan' AS Mes, 'Leste' AS Nome_Regional, 'Banco' AS Nome_Convenio, '560' AS Qtde_Atendimentos
UNION
SELECT 'fev' AS Mes, 'Leste' AS Nome_Regional, 'Banco' AS Nome_Convenio, '300' AS Qtde_Atendimentos

-- select LOUCO funcional mas não otimizado
SELECT
    Nome_Regional = (CASE
                WHEN A.Nome_Regional IS NOT NULL THEN A.Nome_Regional
                WHEN B.Nome_Regional IS NOT NULL THEN B.Nome_Regional
                ELSE C.Nome_Regional
            END),
    Nome_Convenio = (CASE
                WHEN A.Nome_Convenio IS NOT NULL THEN A.Nome_Convenio
                WHEN B.Nome_Convenio IS NOT NULL THEN B.Nome_Convenio
                ELSE C.Nome_Convenio
            END),
    A.Qtde_Atendimentos AS Qtde_Jan,
    B.Qtde_Atendimentos AS Qtde_Fev,
    C.Qtde_Atendimentos AS Qtde_Mar
FROM
    (
        SELECT Nome_Regional, Nome_Convenio, Qtde_Atendimentos
        FROM Teste
        WHERE Mes = 'jan'
    ) A
    FULL JOIN
    (
        SELECT Nome_Regional, Nome_Convenio, Qtde_Atendimentos
        FROM Teste
        WHERE Mes = 'fev'
    ) B        
    ON
        A.Nome_Regional = B.Nome_Regional AND
        A.Nome_Convenio = B.Nome_Convenio
    FULL JOIN
    (
        SELECT Nome_Regional, Nome_Convenio, Qtde_Atendimentos
        FROM Teste
        WHERE Mes = 'mar'
    ) C
    ON
        C.Nome_Regional = A.Nome_Regional AND
        C.Nome_Regional = B.Nome_Regional AND
        C.Nome_Convenio = A.Nome_Convenio AND
        C.Nome_Convenio = B.Nome_Convenio
ORDER BY
    Nome_Regional, Nome_Convenio

Desde já agradeço pela atenção! Obrigado!

[fulvio]

Bom dia Victor,

Como os dados referentes aos meses são dinâmicos, fica um pouco complicado realizar as manipulações com script´s que não sejam dinâmicos.

Se em todos os meses do ano tivesse alguma inserção, você teria que implementar 12 meses no seu select "Louco" (rs...).

Criei duas temporárias e manipulei os dados da tabela "Teste" que passou. Sendo assim, dê carga na tabela e depois rode o script abaixo. Selecione tudo e mande rodar!!

Uma dúvida: tem como ocorrer dois "Nome_Regional" e "Nome_Convenio" iguais, com quantidades diferentes, tipo:

Leste Banco 300

Leste Banco 400

Neste caso, teria que realizar uma somatória nestes valores. A implementação abaixo não realiza isto.

Faça os testes aí... :.)

--Drop table #Total 
--Drop table #Meses 

create table #Total (Controle int Identity, Nome_Regional Varchar(10), Nome_Convenio Varchar(10))
create table #Meses (Mes Varchar(3), Controle int Identity)
Declare @Cont int
Declare @SQL varchar(200)
Set @Cont=1

-- Inserção dos meses na temporaria
Insert into #Meses (Mes)
select distinct Mes from Teste

-- Inserção das colunas meses na tabela Principal
While @Cont <= (select max(controle) from #Meses)
Begin
  Set @SQL = 'alter table #Total add ' + (select Mes from #Meses where Controle=@Cont) + ' int'
  exec  (@SQL)
  Set @Cont = @Cont + 1
End

-- Inserção das Regionais e Convenios
insert into #Total (Nome_Regional, Nome_Convenio)
select distinct Nome_Regional, Nome_Convenio from Teste

Set @Cont=1

-- Inserção da Quantidade Atendimentos
While @Cont <= (select max(controle) from #Meses)
Begin
  Set @SQL = 'update #Total set ' + (select mes from #Meses where controle=@Cont) + 
    '=Qtde_Atendimentos from #Total t, Teste te where t.Nome_Regional=te.Nome_Regional ' +
    'and t.Nome_Convenio=te.Nome_Convenio and te.mes=' + '''' + + (select mes from #Meses where controle=@Cont)+ ''''
  exec (@SQL)
  Select @Cont = @Cont + 1
End

select * from #total