sql - clausola - oracle pivot xml



Pivot dinamico in oracolo sql (4)

Non è possibile inserire un'istruzione dinamica nell'istruzione IN del PIVOT senza utilizzare PIVOT XML, che esegue un'uscita meno auspicabile. Tuttavia, è possibile creare una stringa IN e inserirla nella dichiarazione.

Innanzitutto, qui è la mia tabella di esempio;

  myNumber    myValue myLetter
---------- ---------- --------
         1          2 A        
         1          4 B        
         2          6 C        
         2          8 A        
         2         10 B        
         3         12 C        
         3         14 A      

Impostare prima la stringa da utilizzare nell'istruzione IN. Qui stai inserendo la stringa in "str_in_statement". Stiamo utilizzando COLUMN NEW_VALUE e LISTAGG per impostare la stringa.

clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
    LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT myLetter FROM myTable);

La tua stringa sarà simile a:

'A' AS A,'B' AS B,'C' AS C

Adesso utilizzare l'istruzione String nella query PIVOT.

SELECT * FROM 
    (SELECT myNumber, myLetter, myValue FROM myTable)
    PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));

Ecco l'output:

  MYNUMBER      A_VAL      B_VAL      C_VAL
---------- ---------- ---------- ----------
         1          2          4            
         2          8         10          6 
         3         14                    12 

Ci sono tuttavia delle limitazioni. È possibile concatenare solo una stringa fino a 4000 byte.

https://src-bin.com

... pivot (somma (A) per B in (X))

Ora B è del tipo di dati varchar2 e X è una stringa di valori varchar2 separati da virgole.
I valori per X sono valori distinti selezionati da una colonna (diciamo CL) della stessa tabella. In questo modo funzionava la query di perno.

Ma il problema è che ogni volta che c'è un nuovo valore nella colonna CL devo aggiungere manualmente alla stringa X.

Ho provato a sostituire X con valori selezionati distinti da CL. Ma la query non è in esecuzione.
La ragione che provavo è dovuta al fatto che per la sostituzione di X abbiamo bisogno di valori separati da virgole.
Quindi ho creato una funzione per restituire l'output esatto per corrispondere alla stringa X. Ma la query non funziona ancora.
I messaggi di errore mostrati sono come "missing parantheses righr", "fine del canale di comunicazione file" etc ecc.
Ho provato a pivot xml invece di solo pivot, la query viene eseguita ma fornisce vlaues come oraxxx ecc che non hanno alcun valore.

Forse non lo uso correttamente.
Puoi dirmi un metodo per creare un perno con valori dinamici?


Answer #1

Non è possibile inserire una stringa non costante nella clausola IN clausola pivot.
Puoi utilizzare Pivot XML per questo.

Dalla documentazione :

Subquery Una subquery viene utilizzata solo in combinazione con la parola chiave XML. Quando si specifica una subquery, tutti i valori trovati dalla subquery vengono utilizzati per il pivot

Dovrebbe sembrare come questo:

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;

Puoi anche avere una subquery anziché la parola chiave ANY :

select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;

Ecco una demo di sqlfiddle


Answer #2

Ho usato il metodo di cui sopra (Anton PL / SQL custom pivot ()) e ha fatto il lavoro! Poiché non sono uno sviluppatore Oracle professionale, sono semplici passaggi che ho fatto:

1) Scarica il pacchetto zip per trovare pivotFun.sql in esso. 2) Esegui una volta il pivotFun.sql per creare una nuova funzione 3) Utilizza la funzione in SQL normale.

Basta prestare attenzione ai nomi delle colonne dinamiche. Nel mio ambiente ho trovato che il nome della colonna è limitato con 30 caratteri e non può contenere una singola citazione in esso. Quindi, la mia query è ora qualcosa di simile:

SELECT 
  *
FROM   
  table( 
        pivot('
                SELECT DISTINCT
                    P.proj_id,
                    REPLACE(substr(T.UDF_TYPE_LABEL, 1, 30), '''''''','','') as Attribute,
                    CASE
                      WHEN V.udf_text is null     and V.udf_date is null and      V.udf_number is NOT null  THEN to_char(V.udf_number)
                      WHEN V.udf_text is null     and V.udf_date is NOT null and  V.udf_number is null      THEN to_char(V.udf_date)
                      WHEN V.udf_text is NOT null and V.udf_date is null and      V.udf_number is null      THEN V.udf_text
                      ELSE NULL END
                    AS VALUE
                FROM
                    project   P
                LEFT JOIN UDFVALUE V ON P.proj_id     = V.proj_id 
                LEFT JOIN UDFTYPE  T ON V.UDF_TYPE_ID = T.UDF_TYPE_ID
                WHERE 
                    P.delete_session_id  IS NULL AND
                    T.TABLE_NAME = ''PROJECT''
    ')
)

Funziona bene con record fino a 1m.


Answer #3

Non posso rispondere esattamente alla domanda che l'OP ha chiesto, invece mi descriverò solo come possa essere fatto il perno dinamico.

Qui dobbiamo utilizzare sql dinamico, inizialmente recuperando i valori della colonna in una variabile e passando la variabile all'interno di sql dinamico.

ESEMPIO

Considerate che abbiamo una tabella come sotto.

Se dobbiamo mostrare i valori nella colonna YR come nomi di colonna ei valori in queste colonne da QTY , allora possiamo utilizzare il codice riportato di seguito.

declare
  sqlqry clob;
  cols clob;
begin
  select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
  into   cols
  from   (select distinct YR from EMPLOYEE);


  sqlqry :=
  '      
  select * from
  (
      select *
      from EMPLOYEE
  )
  pivot
  (
    MIN(QTY) for YR in (' || cols  || ')
  )';

  execute immediate sqlqry;
end;
/

RISULTATO

Se necessario, è anche possibile creare una tabella di temp e fare una query di selezione in quella tabella di temp per visualizzare i risultati. È semplice, basta aggiungere il CREATE TABLE TABLENAME AS nel codice precedente.

sqlqry :=
'    
  CREATE TABLE TABLENAME AS
  select * from




pivot