segunda-feira, 9 de abril de 2012

Juntando linhas

Tenho uma tabela de permissões que tem a seguinte forma:

CREATE TABLE PERMISSOES (
  OBJETO VARCHAR2(100),
  USUARIO VARCHAR2(100)
)
Eu preciso de um relatório que enumere todos os usuários para cada objeto, colocando todos os autorizados numa só linha. A minha solução, um pouco complicada, foi juntar as facilidades de consultas hierárquicas do Oracle com um pouco de funções analíticas.

O cerne da consulta é o seguinte SELECT:

select objeto, 
       row_number() over (partition by objeto order by usuario) ordem,
       row_number() over (partition by objeto order by usuario desc) medro,
       usuario
from permissoes
order by 1, 2, 3 desc
Isso produz uma lista de todos os objetos com os respectivos usuários numerados alfabeticamente tanto crescentemente como decrescentemente. Considere as seguintes linhas:

OBJETOUSUARIO
COFREHeráclito
COFREXenofonte
COMPUTADORHeródoto
COMPUTADORHeráclito
COMPUTADORPlatão

A consulta produzirá o resultado abaixo:

OBJETOORDEMMEDROUSUARIO
COFRE12Heráclito
COFRE21Xenofonte
COMPUTADOR13Heráclito
COMPUTADOR22Heródoto
COMPUTADOR31Platão

Envolvendo essa consulta com um SELECT hierárquico, podemos agregar os helenos.

  select objeto, substr(sys_connect_by_path(usuario, ','),2) autorizados 
  from (
    select objeto, 
           row_number() over (partition by objeto order by usuario) ordem,
           row_number() over (partition by objeto order by usuario desc) medro,
           usuario
    from permissoes
  )
  where medro=1
  connect by prior ordem=ordem-1 and prior objeto=objeto
  start with ordem=1
Então, uso a coluna ORDEM para simular uma hierarquia e a coluna MEDRO para eliminar todas as linhas exceto a última (que possui todos os usuarios relacionados a um objeto).

O resultado é o seguinte:

OBJETOAUTORIZADOS
COFREHeráclito,Xenofonte
COMPUTADORHeráclito,Heródoto,Platão

A função sys_connect_by_path() aglutina toda a hierarquia e neste caso indiquei uma vírgula como separador. No entanto, ela coloca uma vírgula também no início e para resolver isso adicionei a função substr().

Nenhum comentário: