Querie com comando similar a TRANSFORM e PIVOT

Question

Boa tarde comunidade do fórum


Estou tentando criar uma querie que tenha as seguintes as cláusulas Transform e Pivot, como fiz essa consulta no Access, preciso passa-la para SQL SERVER 2005 Express, mas não consigo montar a query.


A query no formato do Access fica assim



TRANSFORM Sum(MOVIMENTO.quantidade) AS folhas
SELECT MOVIMENTO.juncao
FROM MOVIMENTO
WHERE (((MOVIMENTO.insumo)=8))
GROUP BY MOVIMENTO.juncao
PIVOT Format([data],"YY/MM");



Parece que TRANSFORM não é reconhecido como comando do SQL Server e a sintaxe do PIVOT não está correta, tentei alguns artigos mas não consegui arrumar, alguém poderia me ajudar?




Obrigado,

Paulo Eduardo Bueno

Answer 1

Boa Tarde,

 

Não é possível converter essa consulta para o SQL Server 2005 Express em uma única instrução SQL. O SQL Server Express possui o operador PIVOT que permite transformar linhas em colunas, mas não com a dinâmica que o Access oferece. Dê uma olhada no link abaixo para alguns exemplos:

 

Dicas e Truques sobre consultas complexas no SQL Server

http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!162.entry

 

[ ]s,

 

Gustavo

Answer 2

Opa Gustavo
Desculpa a demora da resposta heh, é que só voltei pro trabalho hoje,
Então cara

Eu dei uma olhada lá, tem coisas bem interessantes
Mas eu não consegui adaptar para o meu caso aqui.


Teria como exemplificar como esse código ou algum outro qualquer ficaria, eu fiz o seu do exemplo, mas mesmo assim não entendi, comecei a pouco tempo a trabalhar com SQL.


Se puder postar algum outro ou me dar as bases para fazer como esse, quebraria mó galho cara =]



Desde já,

Obrigado
Paulo Eduardo Bueno

Answer 3

Olá Paulo,

 

No blog tem um link para baixar o projeto e no projeto há um script para popular a base de dados. Em todo o caso, se você puder postar a sua situação e alguns scripts, podemos trabalhar com ela.

 

[ ]s,

 

Gustavo

 

Answer 4

Então, eu tenho aqui uns scripts,

Vamos lá


Essa tabela que eu utilizo para criar o PIVOT

CREATE TABLE [dbo].[MOVIMENTO](
    [ch_pr] [int] IDENTITY(1,1) NOT NULL,
    [juncao] [smallint] NULL DEFAULT ((0)),
    [evento] [tinyint] NULL DEFAULT ((0)),
    [insumo] [tinyint] NULL DEFAULT ((0)),
    [quantidade] [int] NULL DEFAULT ((0)),
    [data] [datetime] NULL,
    [carta] [int] NULL DEFAULT ((0)),
    [obs] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL)


Tenho PK no ch_pr


Insumo tenho FK - uma tabela com código do Insumo e tipo de entrada no sistema, nesse caso, tonners e folhas
Evento tenho FK - uma tabela com código do evento e seu tipo (Solicitação, Coleta, atendimento)





Certo, a consulta feita no Access é essa:


TRANSFORM Sum(MOVIMENTO.quantidade) AS folhas
SELECT MOVIMENTO.juncao
FROM MOVIMENTO
WHERE (((MOVIMENTO.insumo)=8))
GROUP BY MOVIMENTO.juncao
PIVOT Format([data],"YY/MM");




Eu gostaria de fazer exatamente essa consulta do Access no SQL Server 2005, mas não estou conseguindo,

Com os dados que tenho, essa consulta no Access gera uma tabela assim:


Junção | 07/12 | 08/01 | 08/02 | 08/03 | 08/04 | 08/05





Bom, basicamente é isso, se precisar de alguma informação mais detalhada, da um toque



Desde já,
Obrigado cara



Paulo Eduardo Bueno

Answer 5

Opa, mais uma dúvida. Eu não posso usar a função Format para definir de certa forma o nome da coluna, assim como faço no Access? Existe alguma outra maneira?



Desde, já
Obrigado


Paulo Eduardo Bueno

Answer 6

Fiz algumas alterações na query, cheguei nisso aqui:


SELECT right(convert(varchar,Movimento.data, 103),7)
AS data , Sum(MOVIMENTO.quantidade) AS folhas,juncao
FROM MOVIMENTO
WHERE MOVIMENTO.insumo=8
GROUP BY juncao
PIVOT (count(juncao)
for
data
in
(data))
as p



Esta acusando erro no PIVOT, nao consegui acertar ainda sua sintaxe, alguém sabe o que posso fazer para arrumar isto?

Answer 7

Olá Paulo,

 

Eu receio que você não irá conseguir o que deseja dessa maneira. Enquanto a cláusula TRANSFORM do Access é dinâmica, a cláusula PIVOT do SQL Server demanda que você especifique intervalo por intervalo. Você não conseguirá fazê-la dinâmicamente da forma que está pretendendo.

 

[ ]s,

 

Gustavo

 

Answer 8

Então como eu deveria setar esses intervalos com datas? Por exemplo, a cada mês?

por exemplo:

MOVIMENTO

ch_pr	juncao	evento	insumo	quantidade	data	carta	obs
1	1	2	3	1	29/5/2007
2	1	2	5	2	29/5/2007
3	1	2	5	1	22/8/2007
4	1	2	3	2	7/8/2007
5	1	2	6	1	26/9/2007
6	1	2	3	1	26/11/2007
7	1	2	3	1	19/12/2007
8	1	2	5	1	7/12/2007

imagina que esse é o select * da tabela movimento,

repare que a mesma junção, enviou (Evento 2) duas informações na mesma data,
como eu poderia agrupar esses valores, e criar um intervalo de tempo? por exemplo, agrupar todos os movimentos de mês em mês (por isso queria que mostrasse apenas MM/yyyy, isso funcionou bem) ? os eventos sempre serão os mesmos para o que eu quero fazer, então pode-se julgar no "Where" pelo menos que seria algo como "evento = 2" e "Group by juncao" creio eu.


Agora essa parte de criar as "colunas" e o Pivot eu não consigo, como poderia fazer isso?



Desde já,
Obrigado comunidade.
Paulo Eduardo Bueno

Answer 9

Boa Tarde,

 

Dê uma olhada no exemplo abaixo:

 

Code Snippet

-- Cria a tabela

CREATE TABLE #TMP (

IDCliente INT, IDPedido INT, Data SMALLDATETIME)

 

-- Insere as tabelas

INSERT INTO #TMP VALUES (1,1,'20080701')

INSERT INTO #TMP VALUES (1,2,'20080805')

INSERT INTO #TMP VALUES (1,3,'20080901')

INSERT INTO #TMP VALUES (1,4,'20080911')

INSERT INTO #TMP VALUES (2,1,'20080801')

INSERT INTO #TMP VALUES (2,3,'20080901')

 

-- Recupera os dados formatados

WITH Cons (Cliente, Pedido, Data)

AS (

SELECT IDCliente, IDPedido,

RIGHT(CONVERT(CHAR(10),Data,103),7)

FROM #TMP)

SELECT Cliente,

[07/2008], [08/2008], [09/2008]

FROM

(SELECT Cliente, Pedido, Data

FROM Cons) AS S

PIVOT

(

COUNT(Pedido)

FOR Data IN ([07/2008], [08/2008], [09/2008])

) AS P

 

 

Observe que no comando de PIVOT, foi necessário especificar explicitamente os meses 7, 8 e 9. Se tivéssemos um mês 10, também teríamos que explicitá-lo. Dessa forma, o PIVOT é um recurso interessante, mas não é tão dinâmico assim. No link que lhe passei demonstrei como tornar esse PIVOT dinâmico, mas é bem trabalhoso.

 

[ ]s,

 

Gustavo

 

Answer 10

Cara, muito obrigado !!!!
isso já deu uma luz no final do túnel heheh

agora eu tenho que ver como tornar isso dinâmico

a primeira parte do PIVOT, na parte de cima onde eu declaro as datas, não teria como eu fazer isso em forma de Between ou algo similiar? Dei uma olhada no seu exemplo, mas, realmente, é algo bem complexo, vou olhar com mais calma e tentar entendê-lo. Mas, se der pra fazer em forma de Between, seria ótimo, porque vou ter que jogar essa querie em uma Stored Procedure pra chamar ela por ReportViewer, aí ficaria fácil tratar as datas como Parâmetro

Mas cara, valeu mesmo, já deu um grande Help!!




Desde já,
Obrigado

Paulo Eduardo Bueno

Answer 11

Cara...teria como você me explicar o código do Pivot dinâmico linha a linha? Tentei entender aqui e nada...


Obrigado,
Paulo Eduardo Bueno

Answer 12

Bom Dia Paulo,

 

O código realmente não é muito simples. A chave para entendê-lo não é simplesmente colá-lo e tentar desvendar as concatenações, etc. Monte um PIVOT estático (aquele que você tem que especificar os valores) e tente perceber que algumas partes desse comando tem de ser dinâmicas. Ex:

 

[07/2008], [08/2008], [09/2008]
FOR Data IN ([07/2008], [08/2008], [09/2008]

 

Esses trechos da consulta precisam ser dinâmicos. Não podemos deixá-los estáticos na consulta. O que o código faz é justamente produzir uma instrução SQL que capture todos os meses e monte o comando final de forma a contemplar todos os meses existentes.

 

No Webcast eu explico mais detalhadamente esse código. Você pode baixá-lo e assistí-lo. Acredito que ficará mais fácil.

 

[ ]s,

 

Gustavo

Answer 13

Então, na sexta feira, eu já tinha feito o estático, só que como o relatório vai ser de 6 meses, correndo do primeiro mês que o usuário Digite...exemplo: ele digita Jan. Aí aparece o consumo de Jan, Fev, Marc e etc...



É possível fazer algo desse tipo? Ou jogar um Between no meio...dá?

Answer 14

Olá Paulo,

 

Esses relatórios tem de ser em tempo real ?

 

[ ]s,

 

Gustavo

 

Answer 15

Então Gustavo


Vai ser assim, o usuário entra com o mês que ele quer, aí eu tenho duas alternativas:

-Ao invés dele colocar apenas um mês como paramêtro,. ele coloca 2 meses....então eu faria um Between entre esses meses, não sei se isso é possível, mas essa era a idéia...


- Ou ele coloca um mês e eu conto +5 meses para fazer trazer os 6 últimos meses




Não sei se alguma dessas duas oções são válidas...mas era esse tipo de controle que eu gostaria de fazer, claro que esses dados estão previamente cadastrados no banco de dados, por isso eu gostaria de chamar uma procedure para preenchê-lo. Só falta isso

 



Desde ja,

Obrigado
Paulo Eduardo

Answer 16

Teria como tu me passar o código do PIVOT dinâmico (com cursor) comentado, para eu tentar adaptar ele nas minhas tabelas?não tem caixa de som aqui na máquina do meu estágio, então não consigo ouvir a aplicação do WebCast.



Obrigado,
Paulo Eduardo Bueno

Answer 17

Olá Paulo,

 

Os comentários que faço são verbais (não são comentários de código) e acho que vendo o Webcast fica mais claro. Tente baixá-lo para ouvir em casa. Acho que fica mais fácil do que eu colocar páginas e páginas de post.

 

[ ]s,

 

Gustavo

 

Answer 18

Tentei cara, mas nada..
não consegui adaptar o PIVOT dinâmico para o meu código


nessas 3 linhas


SET @Marcas = LEFT(@Marcas,LEN(@Marcas)-1)
SET @cmdSQL = @cmdSQL + REPLACE(@cmdSQLFooter,'?',@Marcas)
EXEC(@cmdSQL)

são as três últimas linhas


Teria como eu ver o que tem dentro do @cmdSQL, algum tipo de debug, não sei, alguma forma de ver isso, porque é quando executo que da erro, já dei parser no comando inteiro e nada, então acredito que da algum erro de sintaxe quando ele mistura tudo




Obrigado
Paulo Eduardo Bueno

Answer 19

Olá Paulo,

 

Sim. Isso é possível e até esperado (é difícil prever a saída do comando com exatidão). Ao invés de rodar o exec rode um print @cmdsql

 

[ ]s,

 

Gustavo

 

Answer 20

Obrigado pela resposta rápida Gustavo,
Então cara,
é isso que ta saindo

Code Snippet

SELECT Juncao

, CASE 1 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca1

, CASE 2 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca2

, CASE 3 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca3

, CASE 4 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca4

, CASE 5 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca5

, CASE 6 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca6

, CASE 7 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca7

, CASE 8 WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN 'S' ELSE 'N' END AS Marca8

FROM (

SELECT juncao AS Juncao,[ 1],[ 2],[ 3],[ 4],[ 5],[ 6],[ 7],[ 8]

FROM

(SELECT juncao,

[ 1],[ 2],[ 3],[ 4],[ 5],[ 6],[ 7],[ 8] ,evento from movimento) AS MesData

PIVOT

( SUM(Quantidade) FOR data IN ([ 1],[ 2],[ 3],[ 4],[ 5],[ 6],[ 7],[ 8]) ) AS PivotTable) AS Q

 

 

Não faço a mínima idéia do que está acontecendo aí, se você puder me explicar, o código abaixo é o original

 

 

Code Snippet

DECLARE

@Marca CHAR(1), @Marcas VARCHAR(100), @cmdSQLHeader VARCHAR(500),

@cmdSQLAux VARCHAR(500), @cmdSQLFooter VARCHAR(1000),

@cmdSQL VARCHAR(1500)

SET @cmdSQLHeader = 'SELECT Juncao' + CHAR(10)

SET @cmdSQLAux = ', CASE ? WHEN RIGHT(CONVERT(CHAR(10),data,103),7) THEN ''S'' ELSE ''N'' END AS Marca?' + CHAR(10)

SET @cmdSQLFooter = '

FROM (

SELECT juncao AS Juncao,?

FROM

(SELECT juncao,

? ,evento from movimento) AS MesData

PIVOT

( SUM(Quantidade) FOR data IN (?) ) AS PivotTable) AS Q'

-- Recuperar a lista de marcas existentes

DECLARE cMarcas CURSOR

FOR SELECT distinct insumo FROM Movimento

FAST_FORWARD

 

 

O resto é igual ao seu código de exemplo, acho que ele troxe 8 colunas porque eu especifiquei o insumo no Distinct (tenho 8 tipos de insumo) já tentei com data mas não consegui, alguma sugestão?

 

 

 

Desde já,

Obrigado

Paulo Eduardo Bueno

Answer 21

Cara não teria como utilizar apenas Cursor para fazer isso? Acho que com esse código não vou conseguir implementar no meu problema aqui...Você sabe se da pra fazer apenas com cursor?



Obrigado,
Paulo Eduardo Bueno

Answer 22

Olá Paulo,

 

É possível sim. Você terá que criar uma tabela temporária e definir dinamicamente a criação das colunas. Após criar a tabela temporária, basta popular os dados. Funciona também mas acho que será muito menos performática. Senão me engano o Colla postou um script desses para fazer isso aqui no fórum. Procure pelo termo linhas e colunas na pesquisa do fórum.

 

[ ]s,

 

Gustavo

 

Answer 23

Cara, como que ficaria a clausula do CASE, se eu fosse verificar se o campo é smalldatetime por exemplo? Existe alguma forma de fazer isso?


CASE ? WHEN 1 THEN 'S'...


mudar o "WHEN 1" para algo do tipo, "WHEN Smalldatetime"


Algo desse tipo, só para verificar se é data, porque para eu gerar as colunas dinamicamente aqui, eu tenho que separar elas por data, mas não estou conseguindo





Obrigado,
Paulo Eduardo Bueno

Answer 24

Opa, Gustavo, levando em consideração a solução por você apresentada do PIVOT estático, cara, eu tenho a seguinte dúvida, que, se der certo o que eu estou pensando, creio que meu problema acaba


Seguinte, observer até esse pedaço do código

WITH tbl ( IDjuncao, IDevento, Data, insumo, quantidade)
AS(
SELECT juncao, evento,
RIGHT(CONVERT(CHAR(10),data,103),7), insumo , quantidade/500 as pacotes
FROM movimento)
SELECT IDjuncao,
[12/2007], [02/2008],[03/2008]
...
...

cara, imagina se eu coloco isso numa procedure certo, que tenha como parametrô @datas = varchar, e eu coloco ele no lugar onde ele especifica as datas entre colchetes, ao invés disso eu teria:


WITH tbl ( IDjuncao, IDevento, Data, insumo, quantidade)
AS(
SELECT juncao, evento,
RIGHT(CONVERT(CHAR(10),data,103),7), insumo , quantidade/500 as pacotes
FROM movimento)
SELECT IDjuncao,
@datas
...
...



Bom, julgando que, vou chamar essa proc no VB.NET, onde vou especificar o valor do parametrô, se eu enviar uma String igual a  "[12/2007], [02/2008],[03/2008]"  como parametro, a querie vai funcionar?


Gostaria de saber se você sabe porque ainda não desenvolvi a parte de programação, então se não der, já tiraria essa idéia da minha cabeça e partiria para outra alternativa.



Obrigado,
Paulo Eduardo Bueno

Answer 25

Acho que consegui chegar numa solução

resolvi sair do sql e fazer a consulta direto no VB

Assim, posso usar o método de PIVOT estático para criar as colunas, porém, posso manipular o número de colunas atráves de loops e etc pelo VB.NET

Vou fazer isso amanhã e posto o resultado aqui

Obrigado !

Answer 26

Olá Paulo,

 

O fato é que o PIVOT necessita explicitar as colunas (ainda que a lista seja montada dinamicamente). A questão é que até então tentamos definir essa montagem no SQL e como a dificuldade aumentou, você pode perfeitamente optar pelo VB.

 

Em todo caso, me solidarizei com essa dúvida (e com muitas outras parecidas). Sempre recomendei verificar o Webcast, mas ao olhá-lo (fora os problemas de gravação e visualização do script), me parece que o código não está tão simples quanto eu gostaria que estivesse (ao menos não para boa parte dos interessados).

 

Escrevi um post no meu blog com um exemplo mais simples e melhor comentado que o Webcast. Se ainda estiver interessado, o link é o seguinte:

 

http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!178.entry

 

[ ]s,

 

Gustavo

Answer 27

Opa Gustavo,

Então, ainda não postei o resultado porque tive que fazer outras tarefas de maior importância aqui, então não tive tempo nem de fazer essa consulta no VB ainda, mas, creio que irá funcionar.

Vou dar uma olhada nesse novo exemplo que você criou, e gostaria de agradecer mais uma vez por sempre estar ajudando não só a mim, mas toda a comunidade !

Se eu conseguir adaptar este exemplo para o meu problema, deixo no sql mesmo a consulta, senão, faço ela no VB.Net.

Talvez hoje eu retome essa tarefa!

Obrigado,

Paulo Eduardo Bueno

Answer 28

Gustavo, muito obrigado cara..adaptei o exemplo as minhas necessidades, que no caso seria uma procedure com dois parametrôs de entrada (Mês inicial e Mês final) e funcionou !

Foi uma alteração bem simples, apenas declarei tudo como procedure e ao invés de colocar MIN e MAX, coloquei os parametros de entrada

Ficou assim:

 

Code Snippet

create procedure P_TESTE_PIVOT

@DataMin SMALLDATETIME,

@DataMax SMALLDATETIME

as

DECLARE @menorPeriodo SMALLDATETIME, @maiorPeriodo SMALLDATETIME,

@Periodos VARCHAR(500), @cmdSQL VARCHAR(1000)

-- Captura os perodos e inicializa as variveis

--SELECT @menorPeriodo = MIN(DataPedido), @maiorPeriodo = MAX(DataPedido),

SELECT @menorPeriodo = @DataMin, @maiorPeriodo = @DataMax,

@Periodos = '' FROM tblPedidos

-- Inicializa a varivel @cmdSQL com a montagem do PIVOT

-- O caractr ? ser substitudo pelo perodo obtido dinamicamente

SET @cmdSQL = 'SELECT NomeCliente AS Cliente, ?

FROM

(SELECT NomeCliente, IDPedido, Periodo

FROM vPedidos )

AS TBO

PIVOT

(COUNT(IDPedido) FOR Periodo IN (?)) AS TPVT'

-- Retira os dias das respectivas datas

SET @menorPeriodo = DATEADD(D,-DAY(@menorPeriodo)+1,@menorPeriodo)

SET @maiorPeriodo = DATEADD(D,-DAY(@maiorPeriodo)+1,@maiorPeriodo)

-- Montagem dos perodos

WHILE @menorPeriodo <= @maiorPeriodo

BEGIN

-- Captura o ms e o dia

SET @Periodos = @Periodos + '[' + RIGHT(CONVERT(CHAR(10),@menorPeriodo,103),7) + '],'

-- Adiciona um ms ao menor perodo

SET @menorPeriodo = DATEADD(M,1,@menorPeriodo)

END

-- Monta os perodos

SET @Periodos = LEFT(@Periodos,LEN(@Periodos)-1)

-- Substitui o ? pelo perodo montado dinamicamente

SET @cmdSQL = REPLACE(@cmdSQL,'?',@Periodos)

-- Executa o comando, opcionalmente d um PRINT

-- PRINT @cmdSQL

EXEC (@cmdSQL)

 

 

Agora vou adapta-lo para minhas tabelas, creio que não vou ter problemas em relação a isso

Mais uma vez,

 

Muito Obrigado Gustavo!

 

 

Abraço

 

 

Paulo Eduardo Bueno

Answer 29

Olá Paulo,

 

Quando fiz o Webcast, coloquei o exemplo por conta da necessidade de um colega aqui do fórum. Vi que atendeu perfeitamente a necessidade dele (na verdade ele deve ter copiado e colado o código). Quando fiz esse post, procurei tornar a explicação mais simples, mas dessa vez fiz pensando na sua necessidade (que certamente é muito mais comum). Que bom que lhe atendeu.

 

É gratificante ver essa Thread finalmente respondida.

 

[ ]s,

 

Gustavo