Dispor várias linhas como colunas

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Robson Costa
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 155
Registrado em: Sex, 17 Out 2008 6:05 pm
Localização: Campo Grande - MS

Olá,

Fiz um select que produziu o seguinte resultado

Selecionar tudo

    Prod     Setor     Qtd
---------- --------- --------
     X          A           3
     X          B           2
     Y          A           1
     Z          B           4
Gostaria que em vez de uma coluna setor, tenha várias colunas setores, mostrando o valor da coluna QTD, como no exemplo:

Selecionar tudo

               Prod       Setor A         Setor B    
         -------------  -----------     -------------
                 X              3                 2
                 Y              1                 -
                 Z              -                 4
Observação: a coluna QTD não é resultado da função count(*), mas sim uma coluna original da tabela.

É possível?
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

A partir da versão 11 a oracle disponibilizou a função PIVOT e UNPIVOT que permite transformar LINHAS em COLUNAS e vice-versa.
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Robson

Complementando a resposta do Noctivero,

A) Sem usar o PIVOT

Selecionar tudo

SELECT PROD,
       MAX(QTD_A) AS SETOR_A,
       MAX(QTD_B) AS SETOR_B
  FROM (SELECT PROD,
               CASE WHEN SETOR = 'A' THEN QTD ELSE 0 END AS QTD_A,
               CASE WHEN SETOR = 'B' THEN QTD ELSE 0 END AS QTD_B
          FROM TESTE)
GROUP BY PROD ;                 
B) Com o PIVOT

Selecionar tudo

SELECT *                                                                                                      
FROM   (SELECT PROD,SETOR,QTD 
        FROM   TESTE)                                                                                    
PIVOT  (SUM(QTD) AS QDT_SETOR FOR (SETOR) IN ('A' AS A, 'B' as B))                   
ORDER BY PROD;
Mas note que as queries acima estão "engessadas". Se você tiver 4,5,7 setores, precisará reescrever a queire para se adequar aos setores. Para que a querie fique "dinâmica", você precisaria usar, por exemplo, o PIVOT XML

C) Com o PIVOT XML

Selecionar tudo

SELECT *                                                                                                      
FROM   (SELECT PROD,SETOR,QTD 
        FROM   TESTE)                                                                                    
PIVOT XML (SUM(QTD) AS QDT_SETOR FOR (SETOR) IN (SELECT DISTINCT SETOR FROM TESTE))                   
ORDER BY PROD;
Abraços,

Sergio Coutinho
BCR
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 25
Registrado em: Qui, 22 Nov 2012 1:40 pm
Localização: Patos de Minas
Breno Cristovão Rocha.

boa tarde !

Alguém sabe me dizer porque quando uso o pivot xml eu perco a conexão com o banco de dados ?

o pivot normal eu consigo usar... mas estou precisando de um pivot dinâmico...
BCR
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 25
Registrado em: Qui, 22 Nov 2012 1:40 pm
Localização: Patos de Minas
Breno Cristovão Rocha.

no sql plus ocorre o seguinte erro:

ERRO na linha 65:
ORA-03113: end-of-file on communication channel


já no Toad for Oracle

Unknown error: ORA-6831047



o código do select abaixo:

Selecionar tudo


select *
from ( 
select a.nr_cota nr_cota, 
       a.dt_cota dt_cota, 
       decode(a.status,'A','Aprovado','C','Cancelado','P','Aberto',' ') status,
       initcap(m.nome_prop) prop, 
       initcap(n.nome_fil) filial,
       initcap(o.nome_func) cotador, 
       initcap(p.nome_func) responsavel,
       initcap(t.nome_func) solicitante,
       d.cod_prod || ' - ' || initcap(e.desc_nome||' '||f.desc_varied||' '||g.desc_pen) produto,
       d.cod_prod,
       c.qnt_prod qnt_prod ,
       h.ab ab,
       b.cod_nr cod_forn,
       initcap(nvl(b.desc_forn,k.razao)) forn,
       c.vl_unit vl_unit--,
       --max(x.dt_nf) as dt_nf,
       --max(nvl(x.unit,0)) as unit,
       --c.aprovado, 
       --c.qnt_aprov,
       ---initcap(c.marca) marca
from tscota a
    inner join tscota_forn b
           on a.cod_cota = b.cod_cota
    inner join tscota_prod c
           on b.cod_cota_forn = c.cod_cota_forn
    inner join tprod d
           on c.cod_prod = d.cod_prod
    inner join tnm_prod e
           on d.cod_nome_prod = e.cod_nome_prod 
    left outer join tvaried f
           on d.cod_varied = f.cod_varied
    left outer join tpen g
           on d.cod_pen = g.cod_pen
    inner join tunid h
           on d.cod_unid = h.cod_unid 
    left outer join tficha k
           on b.cod_nr = k.cod_nr
    inner join tprop m
           on a.cod_prop = m.cod_prop
    inner join tfilial n
           on a.cod_fil = n.cod_fil
    inner join tfunc o
           on a.cod_func = o.cod_func
    inner join tfunc p
           on a.cod_resp = p.cod_func              
    inner join tscota_lista q 
           on a.cod_cota = q.cod_cota 
    left outer join tscompra_itens r 
           on q.cod_cota_list = r.cod_cota_list 
    left outer join tscompra s 
           on s.cod_scomp = r.cod_scomp
    left outer join tfunc t
           on s.cod_solic = t.cod_func
where a.cod_cota =  105
group by a.nr_cota,a.dt_cota,decode(a.status,'A','Aprovado','C','Cancelado','P','Aberto',' '),initcap(m.nome_prop), initcap(n.nome_fil),initcap(o.nome_func), initcap(p.nome_func),
               initcap(t.nome_func),
               d.cod_prod || ' - ' || initcap(e.desc_nome||' '||f.desc_varied||' '||g.desc_pen),
               d.cod_prod,
               c.qnt_prod,c.aprovado, c.qnt_aprov,
               h.ab,
               b.cod_nr,initcap(nvl(b.desc_forn,k.razao)),c.vl_unit,c.marca 
order by 10                
) pivot  xml ( max(vl_unit)    for cod_forn in ( select distinct cod_nr  from tscota_forn where cod_cota = 105 )  ) 
order by  10 

Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5023
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Vou colocar aqui alguns links que podem ajudar:
http://glufke.net/oracle/viewtopic.php?p=7584
http://glufke.net/oracle/viewtopic.php?f=6&t=9274

Se você está usando um DBLINK em alguma tabela, tem um texto aqui que pode ajudar:
http://dba-oracle.com/m_ora_03113_end_o ... hannel.htm
BCR
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 25
Registrado em: Qui, 22 Nov 2012 1:40 pm
Localização: Patos de Minas
Breno Cristovão Rocha.

bom dia !
Obrigado pelo retorno dr_gori !

eu olhei os links que você postou logo acima, mas creio que eu não consigo usar o pivot XML porque eu não tenho nenhum tipo de dado especifico para aceitar tags como um arquivo xml , pois diante de todas os lugares que pesquisei sobre o pivot xml ele retorno algo parecido como listado abaixo

Selecionar tudo


<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">210</column></
item><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">90</column></item><
item><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">160</column></item><item>
<column name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY">90</column></item></PivotSet>

creio que deve ser isso...
necessito do pivot xml somente para ele se tornar dinâmico, mas creio que vou buscar outra solução caso não consiga desta forma !
Obrigado !!
Avatar do usuário
fbifabio
Moderador
Moderador
Mensagens: 199
Registrado em: Ter, 22 Fev 2011 1:51 pm
Localização: São Paulo - SP
Contato:
Fábio Prado
www.fabioprado.net

Pessoal,

Ao invés de usar a função PIVOT prefiro usar a função LISTAGG (ver artigo http://www.fabioprado.net/2013/12/funco ... abase.html . A função PIVOT engessa uma qtde fixa de colunas, enquanto que, a LISTAGG, apesar de listar o resultado em uma só coluna, ela permite listar N valores (que são correspondentes a N colunas ao usar PIVOT).

Selecionar tudo

 Por que usar Funções analíticas no Oracle Database?

   No artigo de hoje (último do ano de 2013), vou comentar sobre Funções analíticas e darei um exemplo de como elas podem ser úteis para resolver problemas comuns e otimizar a performance de instruções SQL.

      As funções analíticas foram introduzidas no Oracle 8i e normalmente são utilizadas para executar tarefas que antes eram realizadas por stored procedures ou funções (criadas em PL/SQL), e resolver problemas comuns, como por exemplo, transformar em colunas o resultado de múltiplas linhas (exemplo que será demonstrado mais adiante) ou classificar os valores de uma determinada coluna dentro de cada grupo de um conjunto de linhas (ver exemplos dos links das referências ao final do artigo).
    
     As funções analíticas muitas vezes são confundidas com funções de agregação, portanto, é importante entender que a diferença principal entre elas é que as primeiras retornam múltiplas linhas para cada grupo dentro de uma instrução SQL, enquanto que, as segundas, retornam apenas uma linha para o grupo todo. A grande maioria das funções analíticas podem ser usadas como funções agregadas, e vice-versa. Uma restrição, é que as funções analíticas podem ser usadas somente na lista de colunas ou na cláusula ORDER BY de instruções SELECT.

     Apesar de muitas vezes as funções analíticas não serem muito fáceis de usar, normalmente elas são mais performáticas do que stored procedures ou funções que criamos para resolver o mesmo problema. Além disso, é mais fácil usar algo que já está pronto e é bom, do que tentar reinventar a roda e correr o risco dessa roda sair meio quadrada!

     Alguns exemplos de funções analíticas: AVG, FIRST, LAST, LISTAGG, MAX, MIN, PERCENT_RANK e RANK. Para ver outras funções, consulte as referências.

     Segue abaixo um exemplo de instrução SQL (que eu demonstro nos treinamentos de SQL Tuning) para apresentar uma lista de pedidos e itens de pedidos, que deverá conter a data, código do pedido e código de cada produto (item) do pedido:

      SELECT      P.DT_PEDIDO,
                 P.CD_PEDIDO,
                 I.CD_PRODUTO
     FROM        ECOMMERCE.PEDIDO P
     INNER JOIN  ECOMMERCE.ITEM_PEDIDO I 
            ON   P.CD_PEDIDO = I.CD_PEDIDO
     WHERE       P.CD_PEDIDO between 1900 and 1960
     ORDER BY    1;

    Podemos observar que a instrução SQL acima é bem simples e que o seu resultado também é (ver abaixo o resultado parcial, contendo as 10 primeiras linhas, de um total de 60 que foram retornadas):

DT_PEDIDO                 CD_PEDIDO    CD_PRODUTO
------------------------- ---------------- ------------------
19/09/2008 19:44:38         1956               1 
23/09/2008 19:44:38         1919               1 
23/09/2008 19:44:38         1919               2 
23/09/2008 19:44:38         1919               3 
24/09/2008 19:44:38         1915               3 
24/09/2008 19:44:38         1915               4 
27/09/2008 19:44:38         1960               1 
27/09/2008 19:44:38         1960               2 
27/09/2008 19:44:38         1960               3 
27/09/2008 19:44:38         1960               4 

   No resultado parcial acima podemos observar que a maior parte dos pedidos possui mais de um produto. O pedido de número 1919 possui os produtos de códigos 1, 2 e 3. O pedido de número 1915 possui os produtos de códigos 3 e 4. O produto 1960 possui os produtos de códigos 1, 2, 3 e 4. Na minha opinião, a visualização deste resultado não está muito boa. Não seria mais fácil de ler e entender este resultado se todos os produtos do mesmo pedido retornassem na mesma linha? Eu acredito que sim, portanto, vou transformar o resultado, agrupando os produtos de cada pedido (3º coluna).
  
     Essa transformação será feita inicialmente sem utilizar uma função analítica. Criaremos uma função chamada concatenate_list para agrupar os valores de cada pedido e veremos em seguida o tempo de execução de uma instrução SQL que irá chamá-la. Segue abaixo o código de criação da função concatenate_list e a instrução SQL que executaremos para chamá-la:

     create or replace FUNCTION ECOMMERCE.concatenate_list 
                            (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2
  IS
    l_return  VARCHAR2(32767); 
    l_temp    VARCHAR2(32767);
  BEGIN
    LOOP
      FETCH p_cursor
      INTO  l_temp;
      EXIT WHEN p_cursor%NOTFOUND;
      l_return := l_return || ',' || l_temp;
    END LOOP;

    RETURN LTRIM(l_return, ',');
  END;
   
  SELECT          P.DT_PEDIDO,
                  P.CD_PEDIDO,
                  I.PRODUTOS
  FROM            ECOMMERCE.PEDIDO P
  INNER JOIN  (SELECT I1.CD_PEDIDO,            
                      ECOMMERCE.concatenate_list(
                         CURSOR(SELECT I2.CD_PRODUTO 
                               FROM   ECOMMERCE.ITEM_PEDIDO I2
                               WHERE  I2.CD_PEDIDO = I1.CD_PEDIDO
                               ORDER BY 1)) AS PRODUTOS
               FROM        ECOMMERCE.ITEM_PEDIDO I1
               GROUP BY    I1.CD_PEDIDO) I
      ON       P.CD_PEDIDO = I.CD_PEDIDO
  WHERE        P.CD_PEDIDO between 1900 and 1960
  ORDER BY     1;

   Após executar o SQL acima, veremos o resultado parcial abaixo:
  
DT_PEDIDO                   CD_PEDIDO  PRODUTOS      
------------------------     ------------     --------------
19/09/2008 19:44:38         1956               1             
23/09/2008 19:44:38         1919               1,2,3         
24/09/2008 19:44:38         1915               3,4           
27/09/2008 19:44:38         1960               1,2,3,4       

   O que você achou? Não ficou melhor? Agora temos como resultado 1 linha por pedido e na coluna PRODUTOS temos a relação de todos os produtos do pedido relacionado, separados pelo caractere vírgula. O tempo médio de execução deste SQL (executado 4 vezes) foi de 0,065s. Agora que tal tentarmos melhorar este tempo usando a função analítica LISTAGG? 
  
     A função LISTAGG foi criada no Oracle 11G, portanto, em versões anteriores você deverá ainda deverá utilizar a opção anterior (função concatenate_list ou função similar) ou usar uma função não documentada e sem suporte, chamada WM_CONCAT. Segue abaixo o código da instrução SQL anterior, substituindo o uso da função customizada concatenate_list  pela função analítica LISTAGG:
  
    SELECT    P.DT_PEDIDO,
              P.CD_PEDIDO,
              LISTAGG(I.CD_PRODUTO, ',')
                    within group (ORDER BY 1) PRODUTOS
   FROM       ECOMMERCE.ITEM_PEDIDO I
   INNER JOIN ECOMMERCE.PEDIDO P
       ON     I.CD_PEDIDO = P.CD_PEDIDO
   WHERE      P.CD_PEDIDO = 1960
   GROUP BY   P.DT_PEDIDO, P.CD_PEDIDO
   ORDER BY   1;

    Ao executar a instrução SQL acima, o resultado foi igual ao do SQL anterior, porém existem 2 vantagens em utilizá-la.  A 1ª é que a gente não precisou criar nenhuma função extra (função concatenate_list). A 2ª e principal vantagem foi o ganho no tempo de execução, que caiu para 0.043s (na média de 4 execuções). Resumindo, conseguimos obter o mesmo resultado em um tempo 33,84% mais rápido. 

     Se você ainda não conhecia uma função analítica, minha dica é que você pesquise mais sobre o assunto e passe a utilizá-las sempre que possível! Se você conhecia e já está convencido de que em geral elas são mais performáticas, não há mais desculpas para deixar de usá-las!


Bom pessoal, por hoje é só! 
[]s
Avatar do usuário
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 342
Registrado em: Seg, 29 Jan 2007 7:36 am
Localização: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

e aí galera!

tentei usar o PIVOT mas parece que só funciona na versão 11 do banco, e a minha é a Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

eu preciso transformar o resultado abaixo para o camp PERIODO em colunas e os totais (Qtd) de cada periodo:

Selecionar tudo

ITEM                    PERIODO QTD
-----------------------------------
 ABRACADEIRATIPO 'D'1''	FEB-16	160
 ABRACADEIRATIPO 'D'1''	JUL-16	160
 ABRACADEIRATIPO 'D'1''	SEP-16	610
 ABRACADEIRATIPO 'D'1''	OCT-16	60


É possível deixar assim???:

Selecionar tudo

ITEM                     FEB-16  JUL-16  SEP-16  OCT-16
-------------------------------------------------------
 ABRACADEIRATIPO 'D'1''     160     160     610      60

lembrando que isso será dinâmico, podendo o intervalo de períodos ser variável.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Bom dia Porva,

Segue uma solução feia, mas quebra o galho

Selecionar tudo

Select ITEM, 
       sum(decode(to_Char(periodo,'mm/yyyy'),'06/2016',qtd,0)) qt_jun,
       sum(decode(to_Char(periodo,'mm/yyyy'),'07/2016',qtd,0)) qt_jul,
       sum(decode(to_Char(periodo,'mm/yyyy'),'08/2016',qtd,0)) qt_ago,
       sum(decode(to_Char(periodo,'mm/yyyy'),'09/2016',qtd,0)) qt_set,
       sum(decode(to_Char(periodo,'mm/yyyy'),'10/2016',qtd,0)) qt_out
From   tabela
where  periodo between '01-jun-16' and '30-ago-16'
group by ITEM;
Avatar do usuário
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 342
Registrado em: Seg, 29 Jan 2007 7:36 am
Localização: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

o problema é que não posso usar os períodos fixos no DECODE

o usuário pode querer o relatório no período de JAN-16 a APR-16 , mas também pode solicitar entre OCT-15 a JUL-16, e assim por diante.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Essa é outra solução antiga caso você precise de alguma coisa mais parametrizada.

Selecionar tudo

set serverout on size 1000000

Create Or Replace
Procedure Transpose (
          Myowner       In Varchar2,
          Oldtable      In Varchar2,
          Newtable      In Varchar2,
          Clausula      In Varchar2, 
          Xcol_Dim      In Varchar2,
          Ycol_Dim      In Varchar2,
          Cell_Value    In Varchar2      )  Is
          --
          Cur           Integer        := Dbms_Sql.Open_Cursor;
          Load_Cur      Integer        := Dbms_Sql.Open_Cursor;
          V_Rc          Integer;
          V_Retval      Varchar2(44);
          V_Sql         Varchar2(1000) := '';
          Ycol_Title    Varchar2(33);
          Ycol_Val      Varchar2(22);
          V_Cell_Attr   Varchar2(22)   := 'Number(12,2) ';
/*******************************************************************/
        /* Parse_Stmt: Parses The Passed Sql Statement, Checks For Errors  */
/*******************************************************************/
        Function Parse_Stmt( Cur In Number, V_Sql In Varchar2 ) Return Integer Is
        Begin
          Dbms_Sql.Parse( Cur, V_Sql, Dbms_Sql.V7);
          Return Sqlcode;
        Exception
          When Others Then
           Raise;
          Return Sqlcode ;
        End Parse_Stmt;
/*******************************************************************/
        /* Quotes:  Place Quotes Around A Specific Character Value */
/*******************************************************************/
        Function Quotes(V_Value In Varchar2) Return Varchar2 Is
        Begin
          Return '''' || Upper(V_Value) || '''';
        End Quotes;
/*******************************************************************/
        /* Column_Format: Put All Attributes Of A Column Into A Format */
        /*  Result := Column_Format('Char',3,3,0) */
        /*  Result Returns: Char(3) */
        /*  Result := Column_Format('Number',22,11,2) */
        /*  Result Returns: Number(11,2) */
/*******************************************************************/
        Function Column_Format( Col_Type  In Char,
                                Precision In Char,
                                Maxs      In Char,
                                Scale     In Char)  Return Varchar2 Is
        Begin
          V_Retval  := Upper( Col_Type );       /* Default Is Return As Is */
          If V_Retval In ( 'VARCHAR2', 'CHAR' ) Then
             V_Retval := Col_Type || '(' || Maxs || ')';
          End If;
          If V_Retval In ( 'NUMBER', 'LONG' ) Then
             V_Retval := Col_Type || '(' || Precision || ',' || Scale ||')';
          End If;
          If V_Retval In ( 'DATE' ) Then
             V_Retval := Col_Type ;
          End If;
          Return V_Retval;
        End Column_Format;
/*******************************************************************/
        /* Get_Attr: Obtain Column Attributes To Create Table Columns, Etc.*/
/*******************************************************************/
        Function Get_Attr( Col_Name In Varchar2 ) Return Varchar2 Is
          V_Type      Varchar2(33);
          V_Max       Varchar2(03);
          V_Precision Varchar2(03);
          V_Scale     Varchar2(03);
        Begin
          V_Sql  := 'Select Data_Type, Data_Precision,Char_Col_Decl_Length, Data_Scale ';
          V_Sql  := V_Sql || ' From User_Tab_Columns ';
          V_Sql  := V_Sql || ' Where Table_Name  = ' ||Quotes(Trim(Upper(Oldtable)));
          V_Sql  := V_Sql || '  And  Column_Name = ' ||Quotes(Trim(Upper(Col_Name  )));
          V_Rc   := Parse_Stmt( Cur, V_Sql );
          Dbms_Sql.Define_Column( Cur, 1, V_Type     ,22);
          Dbms_Sql.Define_Column( Cur, 2, V_Precision, 3);
          Dbms_Sql.Define_Column( Cur, 3, V_Max      , 3);
          Dbms_Sql.Define_Column( Cur, 4, V_Scale    , 3);
          V_Rc  := Dbms_Sql.Execute( Cur );
        Loop
          If Dbms_Sql.Fetch_Rows( Cur ) = 0 Then
             Exit;
          End If;
          Dbms_Sql.Column_Value( Cur, 1, V_Type      );
          Dbms_Sql.Column_Value( Cur, 2, V_Precision );
          Dbms_Sql.Column_Value( Cur, 3, V_Max       );
          Dbms_Sql.Column_Value( Cur, 4, V_Scale     );
          V_Retval := Column_Format( V_Type, V_Precision, V_Max,V_Scale);
        End Loop;
          Return V_Retval;
        Exception
          When Others Then
               Raise;
          Return ' ';
        End Get_Attr;
/*******************************************************************/
        /* Drop_Table:  Remove Newtable, If It Exists */
/*******************************************************************/
        Function Drop_Table Return Number Is
        Begin
          Dbms_Sql.Parse( Cur, 'Drop Table ' || Newtable, Dbms_Sql.V7);
          Return Sqlcode;
        Exception
          When Others Then
             If Sqlcode != -942 Then
                Raise;
             End If;
          Return Sqlcode ;
        End Drop_Table;
/*******************************************************************/
        /* Create_Table:  Create Newtable, With List Of X-Column Values */
/*******************************************************************/
        Function Create_Table(Clausula In Varchar2) Return Number Is
        Begin
          V_Sql  := 'Create Table ' || Newtable ;
          V_Sql  := V_Sql ||' As Select Distinct '|| Xcol_Dim ;
          V_Sql  := V_Sql ||' From ' ||  Oldtable ;
          V_Sql  := V_Sql ||Clausula;
          Dbms_Output.Put_Line (V_sql);
          Return Parse_Stmt( Cur, V_Sql );
        End Create_Table;
/*******************************************************************/
        /* Alter_Table:  Append All Distinct Ycol_Dim Values Into Columns*/
/*******************************************************************/
        Function Alter_Table(Clausula In Varchar2)  Return Number Is
        Begin
          V_Cell_Attr := Get_Attr( Cell_Value ); /* Get Cell Column Value Attribute */
          V_Sql := 'Select Distinct '|| Ycol_Dim ||' From ';
          V_Sql :=  V_Sql ||  Oldtable ;
          V_Sql :=  V_Sql ||Clausula;
          V_Rc  := Parse_Stmt( Cur, V_Sql );
          Dbms_Sql.Define_Column( Cur, 1, Ycol_Val, 22);
          V_Rc  := Dbms_Sql.Execute( Cur );
        Loop
          If Dbms_Sql.Fetch_Rows( Cur ) = 0 Then
             Exit;
          End If;
          Dbms_Sql.Column_Value( Cur, 1, Ycol_Val);
          Ycol_Title := Ycol_Dim || '_' || Trim(Ycol_Val);
          V_Sql      := 'Alter Table ' || Newtable || ' Add ';
          V_Sql      := V_Sql || '( '  || Ycol_Title || ' ' ||V_Cell_Attr || ' ) ';
          V_Rc       := Parse_Stmt( Load_Cur, V_Sql );
        End Loop;
          Return 0;
        End Alter_Table;
/*******************************************************************/
        /* Update_Table: Populate Each Intersection, With Cell Columns */
/*******************************************************************/
        Function Update_Table(Clausula In Varchar2) Return Number  Is
        Begin
          V_Sql := 'Select Distinct '|| Ycol_Dim ||' From ' || Oldtable ;
          V_Sql :=  V_Sql ||Clausula;
          V_Rc  := Parse_Stmt( Cur, V_Sql );
          Dbms_Sql.Define_Column( Cur, 1, Ycol_Val, 22);
          V_Rc  := Dbms_Sql.Execute( Cur );
        Loop
          If Dbms_Sql.Fetch_Rows( Cur ) = 0 Then
             Exit;
          End If;
          Dbms_Sql.Column_Value( Cur, 1, Ycol_Val);
          Ycol_Title := Ycol_Dim || '_' || Trim(Ycol_Val);
          V_Sql      := 'Update '  || Newtable   || ' T1 Set ' ||Ycol_Title || ' = ';
          V_Sql      := V_Sql || ' (Select Sum(' || Cell_Value || ')From '  || Oldtable || ' T2';
          V_Sql      := V_Sql || '   Where T1.'  || Xcol_Dim   || '=T2.'    || Xcol_Dim ;
          V_Sql      := V_Sql || '      And    ' || Ycol_Dim   || '= '|| Quotes(Ycol_Val) || ')';
          V_Rc       := Parse_Stmt( Load_Cur, V_Sql );
          V_Rc       := Dbms_Sql.Execute( Load_Cur );
        End Loop;
          Return 0;
        Exception
          When Others Then
            Raise;
            Return 32;
        End Update_Table;
  Begin
    Begin
      V_Rc  := Drop_Table ;                  /* Drop Out Table, If Exists       */
      V_Rc  := Create_Table(Clausula);                 /* Load Distinct X Rows Now*/
      V_Rc  := Alter_Table(Clausula);                  /* Add Y Dimension Columns*/
      V_Rc  := Update_Table(Clausula);       /* Populate Y Columns From Source  */
      Dbms_Sql.Close_Cursor( Cur );
      Dbms_Sql.Close_Cursor( Load_Cur );
    End;
  End Transpose;
/

/*


Exec Transpose ('USUARIO', 'FICHA_COMPENSACAO', 'testenr', ' Where  Dt_Vcto between '||''''||'01-mar-12'||''''||' and '||''''||'20-mar-12'||'''', 'DT_EMIS','DT_VCTO','VL_DOCU');


*/
Anexos
transpose.txt
Transpose
(8.71 KiB) Baixado 622 vezes
Responder
  • Informação