Transformando colunas em linhas II

No artigo anterior, usei expressões regulares para transformar strings com valores separados por vírgula em linhas de uma consulta. Outra utilidade dessa técnica é transformar colunas de uma consulta em linhas.

Por exemplo, considere uma tabela de pessoas que inclui também os nomes dos pais:

  CREATE TABLE PESSOAS (
    NOME VARCHAR2(200),
    NOME_PAI VARCHAR2(200),
    NOME_MAE VARCHAR2(200)
  )

Para transformar isto numa única consulta que retorne os nomes de todas as pessoas referenciadas, podemos juntar com vírgula os nomes linha a linha e depois separá-los com uma expressão regular:

select trim(trailing ',' from nome) from (
  select regexp_substr(
            nome||','||nome_pai||','||nome_mae||','
         ,'[^,]+,',1,n.n) nome
  from pessoas,
      (select rownum n from all_tables where rownum<4) n
)

Deixei a concatenação sozinha numa linha, para evitar a confusão de vírgulas dentro e fora de strings.

A expressão "[^,]+," busca cada pedaço do string que não contenha vírgulas, mas que termine com uma. Por isso, é preciso adicionar uma vírgula ao último item. Por fim, tiram-se as vírgulas e apresentam-se os nomes, linha a linha.