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:

OBJETO	USUARIO
COFRE	Heráclito
COFRE	Xenofonte
COMPUTADOR	Heródoto
COMPUTADOR	Heráclito
COMPUTADOR	Platão

A consulta produzirá o resultado abaixo:

OBJETO	ORDEM	MEDRO	USUARIO
COFRE	1	2	Heráclito
COFRE	2	1	Xenofonte
COMPUTADOR	1	3	Heráclito
COMPUTADOR	2	2	Heródoto
COMPUTADOR	3	1	Platã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:

OBJETO	AUTORIZADOS
COFRE	Heráclito,Xenofonte
COMPUTADOR	Herá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().