Olá, pessoal. Hoje veremos uma solução que utilizei para manipular um conjunto de linhas e colunas no SQL Server com o objetivo de separá-las em dois conjuntos de colunas e facilitar a visualização dos dados, uma operação que geralmente não é fácil de ser realizada nos bancos de dados relacionais. Apesar de utilizar o SQL Server como exemplo, a técnica apresentada nesta coluna pode ser adaptada para outros bancos de dados que suportem a linguagem SQL sem problemas.

Há alguns meses estive envolvido em um projeto para uma
empresa que estava relacionado à área
de telefonia. Um dos objetivos do projeto era facilitar a visualização de
informações sobre ligações telefônicas (logs ou bilhetes) geradas por vários
tipos de dispositivos, tais como equipamentos de PABX, CD-R de centrais
telefônicas e gateways que utilizam a tecnologia VOIP.

As informações disponibilizadas
nesta página permitiam a visualização dos dados coletados pelos equipamentos de
telefonia no final de uma ligação telefônica. As principais colunas do relatório contido na página eram o Número
de A (quem originou a ligação), o Número de B (quem recebeu a ligação), o tempo
de duração de ligação e a data de término. A Figura 1 apresenta a listagem com os dados apresentados da forma como eles são armazenados no banco de dados:

Figura 1. Dados exibidos de forma convencional

O pedido do cliente era visualizar lado a lado as informações
em um relatório de uma página de uma intranet, ocupando de forma mais racional o espaço da tela,
de modo que se pudesse extrair mais conhecimento da informação, como apresentado na Figura 2. Destaco que muitos geradores de relatório (como o Reporting Services, por exemplo) não possuem recursos para realizar esta divisão, pois geralmente eles apresentam os dados da forma que a instrução SELECT trouxe do banco de dados.

Figura 2. Dados organizados em 2 conjuntos de colunas

Durante a elaboração deste relatório desenvolvi uma técnica
para a manipulação das informações utilizando somente instruções SELECT do
Transact-SQL, sem a necessidade de criação de tabelas temporárias ou outras
técnicas adicionais que consumiriam muitos recursos do SQL Server. Durante
alguns períodos, este relatório chegou a mostrar dados com o resultado do
processamento de mais de 10 milhões de linhas, sem apresentar  problemas de consumo excessivo de recursos ou
lentidão. Neste artigo apresentarei uma maneira de dividir os dados entre as colunas
horizontalmente ou verticalmente utilizando a instrução SELECT, com o objetivo
de apresentar os dados de uma maneira mais agradável para os usuários.

A separação lógica das linhas de um relatório pode ser útil
para visualizar os dados, além de economizar a quantidade de papel quando este
é impresso. Imaginem a visualização de um relatório onde temos ordenados
seqüencialmente um funcionário supervisor e um funcionário supervisionado logo
abaixo do supervisor. Ao colocarmos as informações lado a lado, além de
facilitar leitura e acompanhamento dos dados, o leitor do relatório não
precisará fazer várias leituras repetidas para conseguir associar um
determinado funcionário a outro.

Quando temos que apresentar informações na forma de lista,
isto é, em linhas e colunas, geralmente utilizamos a instrução SELECT para
retornar os dados completos da tabela. Podemos limitar a quantidade de dados
retornados utilizando a cláusula WHERE com o objetivo de filtrar certas linhas
para que possamos visualizar somente o que nos interessa. Temos ainda a
cláusula ORDER BY da instrução SELECT que permite a ordenação dos resultados
retornados pela instrução. 

A maioria dos relatórios exige
que, após o filtro e a ordenação dos dados, todas as linhas sejam dividias em
colunas, com o objetivo de facilitar a leitura do relatório. Como exemplo,
vamos trabalhar com a tabela chamada TB_SEPARA, que possui três colunas: uma
coluna chamada ID, contendo uma numeração seqüencial sem falhas, uma coluna
chamada NOME que representa um atributo texto comum de uma tabela e uma
terceira coluna chamada DATA que também representa um atributo comum. A Tabela
1 mostra quatro linhas desta tabela.

Tabela 1. Quatro
linhas da tabela TB_SEPARA.

Neste artigo utilizaremos instruções SELECT baseadas no
dialeto T-SQL utilizado pelo SQL Server, mas com poucas alterações – as
instruções aqui apresentadas podem ser portadas para outros bancos de dados sem
grandes dificuldades.

A primeira manipulação que iremos fazer nestes dados é
duplicá-los fazendo com que o relatório possua seis colunas. Para isso
utilizaremos um INNER JOIN que relacionará a tabela TB_SEPARA com ela mesma,
utilizando um alias chamado A e outra alias chamado B, ambos apontando para a
tabela TB_SEPARA.

O SQL Server permite colunas
com nomes iguais em uma instrução SELECT, mas vamos modificar o nome das novas
colunas para facilitar a manipulação delas por uma aplicação. A instrução
apresentada na Listagem 1 faz a duplicação das colunas e dos dados.

/* DUPLICANDO OS DADOS E INCLUINDO */
/* TRÊS NOVAS COLUNAS NO RESULTADO */
SELECT A.ID, A.NOME, A.DATA
     , B.ID AS ID1 , B.NOME AS NOME1 , B.DATA AS DATA1
FROM TB_SEPARA AS A INNER JOIN TB_SEPARA AS B
ON A.ID=B.ID 

Listagem 1. Instrução SELECT que duplica os dados e as
colunas.

O resultado da execução da instrução apresentada na Listagem
1 pode ser visto na Tabela 2.

Tabela 2. Duplicação
dos dados em novas colunas

Uma duplicação dos dados como a que foi apresentada não faz
muito sentido. Dividiremos os dados colocando-os lado a lado nas colunas
semelhantes, isto é, na primeira linha colocaremos o valor 1 para a coluna ID,
‘A’ para Nome e 16/09/2005 para a coluna Data.

Na mesma linha vamos colocar o valor 2 para a coluna ID1,
‘B’ para NOME1 e 15/09/2005 para a coluna DATA1.

Com os dados neste novo formato gastaríamos apenas duas
linhas no relatório ao invés de quatro para mostrar todos os dados. O leitor do
relatório seguiria uma linha horizontal imaginária durante a leitura,
facilitando o entendimento dos dados e a extração de conhecimento relevante dos
mesmos. A Tabela 3 apresenta os dados no formato desejado, considerando apenas
as quatro linhas da tabela TB_SEPARA.

Tabela 3. Divisão horizontal dos dados através das colunas

Para aplicar esta divisão dos dados e colocá-los neste
formato devemos partir de um caso participar primeiro, ou seja, vamos supor que
a tabela tem uma quantidade par de registros. Depois trataremos do caso onde a
quantidade de registros da tabela seja ímpar.

A idéia utilizada para colocar nos dados neste formato é
pensar em dividir os dados e depois relacioná-los. Analisando os dados da
‘primeira tabela’, aquela que manipulamos através do alias A, podemos perceber
que os ID’s são ímpares. Computacionalmente falando, o resto da divisão por
dois de cada um dos ID’s do alias A deve ser igual a 1. De forma análoga, os
ID’s do alias B devem ser pares, ou seja, o resto da divisão por dois destes
ID’s deve ser zero. A aplicação do operador módulo (%) nos permite efetuar esta
divisão e obter somente as linhas cujos ID’s são pares, para o alias A, e as
linhas cujos ID’s são ímpares, para o alias B.

Até aqui planejamos como iremos separar os dados entre os
alias, que serão implementados através de sub-querys na nossa instrução SELECT.
Porém se somente separamos os dados da tabela nestes dois alias, como a
instrução da Listagem 2 apresenta, não vamos obter o que desejamos.

/* SEPARANDO OS DADOS ENTRE OS  DOIS ALIAS DA INSTRUÇÃO */
SELECT A.ID, A.NOME, A.DATA
     , B.ID AS ID1, B.NOME AS NOME1, B.DATA AS DATA1
FROM ( SELECT * FROM TB_SEPARA WHERE ID%2=1 ) AS A,
     ( SELECT * FROM TB_SEPARA WHERE ID%2=0 ) AS B

Listagem 2. Instrução SELECT que divide os da tabela dados
entre os dois aliases.

O problema da instrução apresentada na Listagem 2 é que o SQL
Server, assim como outros bancos de dados, realiza um CROSS JOIN com os dados
das tabelas utilizadas na instrução SELECT quando não aplicamos uma regra de
join entre as tabelas da instrução (Figura 3). O que precisamos fazer agora é
implementar uma regra de join que relacione corretamente os dados dois alias A
e B.

Figura 3: Resultado da execução da consulta da listagem 2

Analisando os dados das colunas ID e ID1 na Tabela 3 podemos
perceber que o valor da coluna ID1 é igual ao valor da coluna ID mais um. Esta
será a nossa regra do join, que pode ser vista na instrução SELECT da Listagem
3. Na cláusula ON a coluna ID para o alias B foi utilizada, pois é somente na
lista de colunas da instrução que trocamos o nome da coluna para ID1.

/* APLICANDO O CRITÉRIO DE JOIN  NA INSTRUÇÃO SELECT */
SELECT A.ID, A.NOME, A.DATA
     , B.ID AS ID1, B.NOME AS NOME1, B.DATA AS DATA1
FROM       ( SELECT * FROM TB_SEPARA WHERE ID%2=1 ) AS A
INNER JOIN ( SELECT * FROM TB_SEPARA WHERE ID%2=0 ) AS B
ON  A.ID+1=B.ID

Listagem 3. Instrução SELECT com o critério de join

Vale notar que esta divisão dos dados poderia ser facilmente
implementada através da codificação da lógica de separação dos dados
diretamente na aplicação ao invés de se utilizar o banco de dados. Se fizermos
isso, claramente estaremos fazendo um processamento linha a linha e não
utilizaremos todas as vantagens do otimizador de consultas do SQL Server, que
pode efetuar as operações necessárias de
maneira mais otimizada e, dependendo da arquitetura da aplicação, deixar o
processamento no lado do servidor.

Executando a instrução SELECT da Listagem 3 obteremos os
dados da maneira que desejamos e que foram mostrados na Tabela 3. Porém devemos
considerar ainda o caso onde o número de linhas da tabela é ímpar.

Neste caso é preciso definir como a linha ‘a mais’ será
mostrada. Uma maneira é definir que esta linha ‘a mais’ será inserida no
primeiro conjunto de linhas (colunas do alias A) e deixar as colunas
correspondentes do segundo conjunto de linhas (colunas do alias B) com o valor
NULL. As colunas que possuírem valor NULL no resultado final podem ser trocadas
na aplicação que apresenta os dados ou através do uso da estrutura CASE do SQL
Server.

A modificação necessária para tratar o caso onde a quantidade
de linhas da tabela é ímpar na instrução SELECT é simples: basta utilizar um
LEFT OUTTER JOIN, ou seja, indicar na instrução SELECT que as linhas do alias A
que não se relacionarem com as linhas do alias B também devem ser incluídas no
resultado. A instrução final é apresentada na Listagem 4.

/* UTILIZANDO O LEFT OUTTER JOIN  NA INSTRUÇÃO SELECT */
SELECT A.ID, A.NOME, A.DATA
     , B.ID AS ID1, B.NOME AS NOME1, B.DATA AS DATA1
FROM            ( SELECT * FROM TB_SEPARA WHERE ID%2=1 ) AS A
LEFT OUTER JOIN ( SELECT * FROM TB_SEPARA WHERE ID%2=0 ) AS B
ON A.ID+1=B.ID

Listagem 4. Tratando a quantidade ímpar de linhas com o LEFT
OUTTER JOIN.

O LEFT OUTTER JOIN utilizado na instrução da Listagem 4 pode
ser trocado por um RIGHT OUTTER JOIN, desde que a ordem das tabelas criadas
pelas subqueries também seja trocada. Supondo que a tabela TB_SEPARA tenha
cinco linhas, a instrução SELECT da Listagem 4 irá transformar os dados para o
formato exibido na Tabela 4.

Tabela 4. Divisão horizontal da quantidade ímpar de dados

Uma vez que conseguimos dividir os dados da tabela de modo
que a leitura do usuário siga uma linha horizontal imaginária, podemos também
implementar uma solução onde as linhas da tabela sejam divididas de modo
vertical, ou seja, o usuário deve seguir
uma linha vertical imaginária. A Tabela 5 apresenta os dados no formato
vertical, considerando apenas as quatro linhas da tabela TB_SEPARA.

Tabela 5. Divisão vertical dos dados através das colunas

Seguindo a mesma idéia da solução horizontal devemos dividir
os dados da nossa tabela em dois alias, A e B. Porém a divisão desta vez será
um pouco diferente: o alias A conterá a primeira metade dos dados e o alias B
conterá a segunda metade dos dados. Mais uma vez montaremos a instrução
pensando inicialmente que a tabela contém uma quantidade par de linhas.

Para obter a divisão dos dados da maneira descrita devemos
levar em consideração que a tabela contém uma coluna chamada ID cujos valores
são seqüenciais sem falhas. Para obter a quantidade exata de cada alias devemos
utilizar a função de agregação COUNT() e dividir o valor retornado por dois.
Para evitar problemas de arredondamento utilizaremos a função CEILING() que
arredonda o valor para o próximo número inteiro. Veja a instrução abaixo:

SELECT CEILING(COUNT(*)/2.0) FROM TB_SEPARA

Ela irá retornar a quantidade de linhas na tabela dividida
por dois. Se utilizarmos esta técnica em uma tabela onde a quantidade de linhas
é conhecida e fixa não precisaremos utilizar a instrução acima na query final,
tornando-a mais simples. Caso contrário devemos utilizar esta instrução como
uma subquery em três pontos, como veremos a seguir.

Utilizando o valor retornado pela função CEILING() vamos
comparar o ID da tabela para gerar os dois alias. Para o alias A os valores da
coluna ID devem ser menores ou iguais à metade da quantidade de dados da
tabela. Para o alias B os valores da coluna ID devem ser maiores que a metade
da quantidade de dados da tabela. A listagem 5 mostra a criação dos alias A e B
gerados de acordo com a quantidade de linhas
da tabela.

/* DIVIDINDO OS DADOS DA TABELA EM DUAS METADES (ALIAS A E B ) */
SELECT A.*,B.*
FROM (SELECT * 
      FROM TB_SEPARA 
      WHERE ID <= (SELECT CEILING(COUNT(*)/2.0) FROM TB_SEPARA) ) AS A,
     (SELECT * 
      FROM TB_SEPARA 
      WHERE ID >  (SELECT CEILING(COUNT(*)/2.0) FROM TB_SEPARA) )  AS B

Listagem 5. Instrução SELECT que divide em duas metades os
dados da tabela TB_SEPARA

De maneira análoga ao relacionamento descrito na separação
horizontal, analisando os dados das colunas ID e ID1 na Tabela 5 podemos
perceber que o valor da coluna ID1 é igual ao valor da coluna ID mais o valor
da metade da quantidade de linhas da tabela. Por exemplo: o valor da coluna ID1
da primeira linha é três, que é o valor da coluna ID na primeira linha, um,
somado a dois que é o valor da metade de linhas da tabela TB_SEPARA.

Definida a regra de join vamos aplicar um LEFT OUTTER JOIN
com o objetivo de trazer todas as linhas que por ventura não caiam na restrição
do join junto ao alias A, ou seja, em caso de quantidade ímpar de linhas na
tabela, a linha ‘a mais’ será mostrada à
esquerda. A instrução final pode ser vista na listagem 6, e a tabela 6 mostra a
execução da instrução quando a tabela TB_SEPARA contém 5 linhas.

/* RELACIONANDO AS DUAS  METADES DA TABELA  */
SELECT A.*,B.*
FROM (SELECT * 
      FROM TB_SEPARA 
      WHERE ID <= (SELECT CEILING(COUNT(*)/2.0) FROM TB_SEPARA) ) AS A
LEFT OUTER JOIN
     (SELECT * 
      FROM TB_SEPARA 
      WHERE ID >  (SELECT CEILING(COUNT(*)/2.0) FROM TB_SEPARA) )  AS B
ON  A.ID + (SELECT CEILING(COUNT(*)/2.0) FROM TB_SEPARA)  = B.ID  

Listagem 6. Tratando a quantidade ímpar de linhas com o LEFT
OUTTER JOIN.

Tabela 6. Divisão
vertical da quantidade ímpar de dados.

Para concluir, neste artigo apresentei uma implementação que separa
horizontalmente e verticalmente os dados de uma tabela através da criação de
novas colunas e alias, desde que haja uma coluna na tabela que apresente uma
numeração seqüencial sem falhas ou repetições. Algumas instruções SELECT foram
apresentadas para implementar a separação horizontal e vertical dos dados
utilizando o dialeto T-SQL, que pode ser facilmente convertido para outros
dialetos do SQL.