[Dica] Pivot Tables

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:

Teve uma matéria em uma Oracle Magazine sobre o assunto. (se eu achar o ano/mês, posto aqui).

Exemplo de Pivot Table

Selecionar tudo

SELECT A8.X+A7.X+A6.X+A5.X+A4.X+A3.X+A2.X+A1.X PIVOT FROM
  (SELECT 0 X FROM DUAL UNION SELECT 1 FROM DUAL) A1,
  (SELECT 0 X FROM DUAL UNION SELECT 10 FROM DUAL) A2,
  (SELECT 0 X FROM DUAL UNION SELECT 100 FROM DUAL) A3,
  (SELECT 0 X FROM DUAL UNION SELECT 1000 FROM DUAL) A4,
  (SELECT 0 X FROM DUAL UNION SELECT 10000 FROM DUAL) A5,
  (SELECT 0 X FROM DUAL UNION SELECT 100000 FROM DUAL) A6,
  (SELECT 0 X FROM DUAL UNION SELECT 1000000 FROM DUAL) A7,
  (SELECT 0 X FROM DUAL UNION SELECT 10000000 FROM DUAL) A8
/

ou octal

SELECT CEM.X+DEZ.X+UM.X FROM
  (SELECT 0 X FROM DUAL
   UNION SELECT 1 FROM DUAL
   UNION SELECT 2 FROM DUAL
   UNION SELECT 3 FROM DUAL
   UNION SELECT 4 FROM DUAL
   UNION SELECT 5 FROM DUAL
   UNION SELECT 6 FROM DUAL
   UNION SELECT 7 FROM DUAL) UM,
  (SELECT 0 X FROM DUAL
   UNION SELECT 10 FROM DUAL
   UNION SELECT 20 FROM DUAL
   UNION SELECT 30 FROM DUAL
   UNION SELECT 40 FROM DUAL
   UNION SELECT 50 FROM DUAL
   UNION SELECT 60 FROM DUAL
   UNION SELECT 70 FROM DUAL) DEZ,
  (SELECT 0 X FROM DUAL
   UNION SELECT 100 FROM DUAL
   UNION SELECT 200 FROM DUAL
   UNION SELECT 300 FROM DUAL
   UNION SELECT 400 FROM DUAL
   UNION SELECT 500 FROM DUAL
   UNION SELECT 600 FROM DUAL
   UNION SELECT 700 FROM DUAL) CEM
/
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 11 visitantes