Usuário com melhor resposta
Montar Select

Pergunta
-
Pessoal,
Tenho 4 tabelas (tabela2011, tabela2010, tabela2009 e tabela2008) com os mesmos campos em cada (empresa, ano, trimestre, valor). Preciso fazer um select para mostrar os seguintes dados:
exemples de dados de uma tabela
Empresa | Ano | Trimestre | Valor
Vale | 2010 | 1 | 2.000,00
Vale | 2010 | 2 | 3.000,00
Preciso montar uma select que retorne as seguintes colunas
Empresa | Valor2010_Trimestre1 | Valor2010_Trimestre2 | Valor2010_Trimestre3 | Valor2011_Trimestre1 | Valor2011_Trimestre2 | Valor2011_Trimestre3
sENDO QUE Valor2010_Trimestre1 não existe em nenhuma das tabelas, teria que ser um where com trimestre = 1 ou 2 ou 3 e ano = 2010....
Entenderam?
Luiz Henroqie
Respostas
-
Resolvi da seguinte maneira
SELECT X.CODIGO AS CODIGO
,X.EMPRESA AS EMPRESA
,SUBSTRING(X.CONTA,5,15) AS CONTA
,X.NOME AS NOME
,SUM(X.VALOR2010_TRIMESTRE1) AS VALOR2010_TRIMESTRE1
,SUM(X.VALOR2010_TRIMESTRE2) AS VALOR2010_TRIMESTRE2
,SUM(X.VALOR2010_TRIMESTRE3) AS VALOR2010_TRIMESTRE3
,SUM(X.VALOR2010_TRIMESTRE4) AS VALOR2010_TRIMESTRE4
,SUM(X.VALOR2009_TRIMESTRE1) AS VALOR2009_TRIMESTRE1
,SUM(X.VALOR2009_TRIMESTRE2) AS VALOR2009_TRIMESTRE2
,SUM(X.VALOR2009_TRIMESTRE3) AS VALOR2009_TRIMESTRE3
,SUM(X.VALOR2009_TRIMESTRE4) AS VALOR2009_TRIMESTRE4
FROM (
SELECT A.CODIGO AS CODIGO
,A.EMPRESA AS EMPRESA
,A.CONTA AS CONTA
,A.NOME AS NOME
,SUM(CASE WHEN A.TRIMESTRE = 1 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE1
,SUM(CASE WHEN A.TRIMESTRE = 2 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE2
,SUM(CASE WHEN A.TRIMESTRE = 3 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE3
,SUM(CASE WHEN A.TRIMESTRE = 4 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE4
,0.00 AS VALOR2009_TRIMESTRE1
,0.00 AS VALOR2009_TRIMESTRE2
,0.00 AS VALOR2009_TRIMESTRE3
,0.00 AS VALOR2009_TRIMESTRE4
FROM TABELA2010 A WITH(NOLOCK)
GROUP BY A.EMPRESA,A.CODIGO,A.CONTA,A.NOME
UNION ALL
SELECT A.CODIGO AS CODIGO
,A.EMPRESA AS EMPRESA
,A.CONTA AS CONTA
,A.NOME AS NOME
,0.00 AS VALOR2010_TRIMESTRE1
,0.00 AS VALOR2010_TRIMESTRE2
,0.00 AS VALOR2010_TRIMESTRE3
,0.00 AS VALOR2010_TRIMESTRE4
,SUM(CASE WHEN A.TRIMESTRE = 1 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE1
,SUM(CASE WHEN A.TRIMESTRE = 2 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE2
,SUM(CASE WHEN A.TRIMESTRE = 3 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE3
,SUM(CASE WHEN A.TRIMESTRE = 4 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE4
FROM TABELA2009 A WITH(NOLOCK)
GROUP BY A.EMPRESA,A.CODIGO,A.CONTA,A.NOME
)X
GROUP BY X.CODIGO
,X.EMPRESA
,SUBSTRING(X.CONTA,5,15)
,X.NOMELuiz Henroqie
- Marcado como Resposta Luizhcota terça-feira, 22 de janeiro de 2013 05:43
Todas as Respostas
-
Boa noite,
Experimente desta forma:
with CTE_U as ( select * from Tabela2010 union all select * from Tabela2011 ) select Empresa, sum(case when Ano = 2010 and Trimestre = 1 then Valor else 0 end) as Valor2010_Trimestre1, sum(case when Ano = 2010 and Trimestre = 2 then Valor else 0 end) as Valor2010_Trimestre2, sum(case when Ano = 2010 and Trimestre = 3 then Valor else 0 end) as Valor2010_Trimestre3, sum(case when Ano = 2010 and Trimestre = 4 then Valor else 0 end) as Valor2010_Trimestre4, sum(case when Ano = 2011 and Trimestre = 1 then Valor else 0 end) as Valor2011_Trimestre1, sum(case when Ano = 2011 and Trimestre = 2 then Valor else 0 end) as Valor2011_Trimestre2, sum(case when Ano = 2011 and Trimestre = 3 then Valor else 0 end) as Valor2011_Trimestre3, sum(case when Ano = 2011 and Trimestre = 4 then Valor else 0 end) as Valor2011_Trimestre4 from CTE_U group by Empresa
Espero que ajude.
Assinatura: http://www.imoveisemexposicao.com.br
-
Resolvi da seguinte maneira
SELECT X.CODIGO AS CODIGO
,X.EMPRESA AS EMPRESA
,SUBSTRING(X.CONTA,5,15) AS CONTA
,X.NOME AS NOME
,SUM(X.VALOR2010_TRIMESTRE1) AS VALOR2010_TRIMESTRE1
,SUM(X.VALOR2010_TRIMESTRE2) AS VALOR2010_TRIMESTRE2
,SUM(X.VALOR2010_TRIMESTRE3) AS VALOR2010_TRIMESTRE3
,SUM(X.VALOR2010_TRIMESTRE4) AS VALOR2010_TRIMESTRE4
,SUM(X.VALOR2009_TRIMESTRE1) AS VALOR2009_TRIMESTRE1
,SUM(X.VALOR2009_TRIMESTRE2) AS VALOR2009_TRIMESTRE2
,SUM(X.VALOR2009_TRIMESTRE3) AS VALOR2009_TRIMESTRE3
,SUM(X.VALOR2009_TRIMESTRE4) AS VALOR2009_TRIMESTRE4
FROM (
SELECT A.CODIGO AS CODIGO
,A.EMPRESA AS EMPRESA
,A.CONTA AS CONTA
,A.NOME AS NOME
,SUM(CASE WHEN A.TRIMESTRE = 1 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE1
,SUM(CASE WHEN A.TRIMESTRE = 2 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE2
,SUM(CASE WHEN A.TRIMESTRE = 3 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE3
,SUM(CASE WHEN A.TRIMESTRE = 4 AND A.ANO = 2010
THEN A.VALOR
ELSE 0.00
END) AS VALOR2010_TRIMESTRE4
,0.00 AS VALOR2009_TRIMESTRE1
,0.00 AS VALOR2009_TRIMESTRE2
,0.00 AS VALOR2009_TRIMESTRE3
,0.00 AS VALOR2009_TRIMESTRE4
FROM TABELA2010 A WITH(NOLOCK)
GROUP BY A.EMPRESA,A.CODIGO,A.CONTA,A.NOME
UNION ALL
SELECT A.CODIGO AS CODIGO
,A.EMPRESA AS EMPRESA
,A.CONTA AS CONTA
,A.NOME AS NOME
,0.00 AS VALOR2010_TRIMESTRE1
,0.00 AS VALOR2010_TRIMESTRE2
,0.00 AS VALOR2010_TRIMESTRE3
,0.00 AS VALOR2010_TRIMESTRE4
,SUM(CASE WHEN A.TRIMESTRE = 1 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE1
,SUM(CASE WHEN A.TRIMESTRE = 2 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE2
,SUM(CASE WHEN A.TRIMESTRE = 3 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE3
,SUM(CASE WHEN A.TRIMESTRE = 4 AND A.ANO = 2009
THEN A.VALOR
ELSE 0.00
END) AS VALOR2009_TRIMESTRE4
FROM TABELA2009 A WITH(NOLOCK)
GROUP BY A.EMPRESA,A.CODIGO,A.CONTA,A.NOME
)X
GROUP BY X.CODIGO
,X.EMPRESA
,SUBSTRING(X.CONTA,5,15)
,X.NOMELuiz Henroqie
- Marcado como Resposta Luizhcota terça-feira, 22 de janeiro de 2013 05:43