AJUDA URGENTE COM SELECT

DEFAVERO 25/03/2010 10:53:30
#337856

Srs,

Criei um select para contar a produção, porém o select ficou tão grande de deu erro ao tentar executar.... O que estou precisando é fazer com que minha tabela faça de linhas colunas... (Tipo uma tabela dinâmica)

Segue o select

sSQL = [Ô] SELECT POSTO, (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]22[ô] AND POSTO = [ô]POSTO01[ô]) AS 22,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]23[ô] AND POSTO = [ô]POSTO01[ô]) AS 23,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]0[ô] AND POSTO = [ô]POSTO01[ô]) AS 00,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]1[ô] AND POSTO = [ô]POSTO01[ô]) AS 01,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]2[ô] AND POSTO = [ô]POSTO01[ô]) AS 02,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]3[ô] AND POSTO = [ô]POSTO01[ô]) AS 03,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]4[ô] AND POSTO = [ô]POSTO01[ô]) AS 04,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]5[ô] AND POSTO = [ô]POSTO01[ô]) AS 05,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]6[ô] AND POSTO = [ô]POSTO01[ô]) AS 06,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]7[ô] AND POSTO = [ô]POSTO01[ô]) AS 07,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]8[ô] AND POSTO = [ô]POSTO01[ô]) AS 08,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]9[ô] AND POSTO = [ô]POSTO01[ô]) AS 09,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]10[ô] AND POSTO = [ô]POSTO01[ô]) AS 10,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]11[ô] AND POSTO = [ô]POSTO01[ô]) AS 11,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]12[ô] AND POSTO = [ô]POSTO01[ô]) AS 12,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]13[ô] AND POSTO = [ô]POSTO01[ô]) AS 13,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]14[ô] AND POSTO = [ô]POSTO01[ô]) AS 14,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]15[ô] AND POSTO = [ô]POSTO01[ô]) AS 15,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]16[ô] AND POSTO = [ô]POSTO01[ô]) AS 16,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]17[ô] AND POSTO = [ô]POSTO01[ô]) AS 17,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]18[ô] AND POSTO = [ô]POSTO01[ô]) AS 18,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]19[ô] AND POSTO = [ô]POSTO01[ô]) AS 19,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]20[ô] AND POSTO = [ô]POSTO01[ô]) AS 20,[Ô]
sSQL = sSQL & [Ô] (SELECT SUM(ROWCONT) FROM [RELATORIO$] WHERE HOUR(HORAINI) = [ô]21[ô] AND POSTO = [ô]POSTO01[ô]) AS 21[Ô]
sSQL = sSQL & [Ô] FROM [RELATORIO$] WHERE POSTO = [ô]POSTO01[ô] GROUP BY POSTO[Ô]

Vou unindo os select[ô]s criando novas linhas... Problema é que tenho quase 30 postos de trabalho, então isso fica gigante e não executa....meu banco é Access(Não tem como mudar)... tentei fazer alguns testes com Pivot mas não funciona...

Alguém tem alguma idéia de como posso fazer isso de alguma outra maneira????

To meio desesperado a procura...


So meio leigo ainda no assunto... Mas estamos ai!

att,