[Dica] Pivot Tables

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Mensagemem Sáb, 15 Mai 2004 7:14 pm

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

Exemplo de Pivot Table

Código: Selecionar todos
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
/
dr_gori
Localização: Portland, OR USA


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem

Voltar para SQL

Quem está online

Usuários navegando neste fórum: Google [Bot] e 5 visitantes