none
Creare una nuova tabella con la funzione DynamicPivotQuery RRS feed

  • Domanda

  • Buonasera, dopo aver creato formattato una query utilizzando la funzione DynamicPivotQuery, dovrei creare una nuova tabella nel mio db. A seguire la query che con l'attuale formattazione mi restituisce una tabella che non tiene conto della funzione di pivot.


    SELECT
    ANNO,
    MESE,
    T0.CLIENTE,
    CLIENTE_RAGSOC,
    VIAGGIO_CLIENTE,
    VIAGGIO_VETTORE,
    CARICO_DATA_ORA,
    CARICO_LOCALITA,
    CARICO_ZONA,
    SCARICO_DATA_ORA,
    SCARICO_LOCALITA,
    SCARICO_ZONA,
    T0.VETTORE,
    VETTORE_RAGSOC,
    PARTENZA_DATA_ORA,
    PARTENZA_LOCALITA,
    PARTENZA_ZONA,
    PARTENZA_CAP,
    V_CARICO_DATA_ORA,
    V_CARICO_LOCALITA,
    V_CARICO_ZONA,
    V_CARICO_CAP,
    V_SCARICO_DATA_ORA,
    V_SCARICO_LOCALITA,
    V_SCARICO_ZONA,
    V_SCARICO_CAP,
    ARRIVO_DATA_ORA,
    ARRIVO_LOCALITA,
    ARRIVO_ZONA,
    ARRIVO_CAP,
    t1.ORDINE_PRIMO_CARICO_NAZIONE_DESCRIZIONE AS NAZIONE_CARICO,
    t1.ORDINE_ULTIMO_SCARICO_NAZIONE_DESCRIZIONE AS NAZIONE_SCARICO,
    NUMERO_INTERNO,
    NUMERO_INTERNO_ABBINATO,
    VEICOLO_SEDE_GESTIONE,
    COSTO_COD_DESCRIZIONE,
    VALORE

    into #CoIn

    FROM [SgaM_Analisi].[dbo].[SMC_T2COIN_TAB_analisi] T0
    left outer join [SgaM_Analisi].[dbo].[SMC_SHIPPINGRUN_analisi] T1                              

    on
    (T0.CLIENTE_VIAGGIO_ANNO=T1.ORDINE_ANNO
    and T0.CLIENTE_VIAGGIO_FILIALE=T1.ORDINE_FILIALE
    and T0.CLIENTE_VIAGGIO_NUMERO=T1.ORDINE_NUMERO
    and T0.CLIENTE_VIAGGIO_NUMERO_CARICO_SCARICO=T1.ORDINE_NUMERO_CARICO_SCARICO
    and T0.VETTORE_VIAGGIO_ANNO=T1.VIAGGIO_ANNO
    and T0.VETTORE_VIAGGIO_FILIALE=T1.VIAGGIO_FILIALE
    and T0.VETTORE_VIAGGIO_NUMERO=T1.VIAGGIO_NUMERO)

    where ANNO=2020 and MESE=11
    and NUMERO_INTERNO in ('AG53TOR','AG73TOR')

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @ColumnName AS NVARCHAR(MAX),
    @PivotSelectColumnName AS NVARCHAR(MAX)

    /************************************************************************************************
    SELEZIONO I VALORI DISTINTI PER LA COLONNA PIVOT
    ************************************************************************************************/
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           +QUOTENAME(COSTO_COD_DESCRIZIONE)
    FROM (SELECT DISTINCT COSTO_COD_DESCRIZIONE FROM #CoIn) AS COSTO_COD_DESCRIZIONE 

    /************************************************************************************************
    SELEZIONO I VALORI DISTINTI PER LA COLONNA PIVOT QUANDO NULLI E LI SOSTITUISCO CON ZERO
    ************************************************************************************************/
    SELECT @PivotSelectColumnName 
    = ISNULL(@PivotSelectColumnName + ',','')
    + 'ISNULL(' + QUOTENAME(COSTO_COD_DESCRIZIONE) + ',0) AS '
    + QUOTENAME(COSTO_COD_DESCRIZIONE)
    FROM (SELECT DISTINCT COSTO_COD_DESCRIZIONE FROM #CoIn) 
    AS COSTO_COD_DESCRIZIONE ORDER BY COSTO_COD_DESCRIZIONE
    /************************************************************************************************
    CREO LA PIVOT DINAMICA
    ************************************************************************************************/
    SET @DynamicPivotQuery = 
    'SELECT *FROM 
    (
    SELECT

    ANNO,MESE,CLIENTE,CLIENTE_RAGSOC,VIAGGIO_CLIENTE,VIAGGIO_VETTORE,CARICO_DATA_ORA,CARICO_LOCALITA,CARICO_ZONA,
    SCARICO_DATA_ORA,SCARICO_LOCALITA,SCARICO_ZONA,VETTORE,VETTORE_RAGSOC,PARTENZA_DATA_ORA,PARTENZA_LOCALITA,PARTENZA_ZONA,
    PARTENZA_CAP,V_CARICO_DATA_ORA,V_CARICO_LOCALITA,V_CARICO_ZONA,V_CARICO_CAP,V_SCARICO_DATA_ORA,V_SCARICO_LOCALITA,V_SCARICO_ZONA,
    V_SCARICO_CAP,ARRIVO_DATA_ORA,ARRIVO_LOCALITA,ARRIVO_ZONA,ARRIVO_CAP,NAZIONE_CARICO,NAZIONE_SCARICO,NUMERO_INTERNO,
    NUMERO_INTERNO_ABBINATO,VEICOLO_SEDE_GESTIONE,COSTO_COD_DESCRIZIONE,VALORE

    FROM #CoIn
    ) SRC
    PIVOT 
    (
    SUM(VALORE) FOR COSTO_COD_DESCRIZIONE IN (' + @ColumnName + ')
    ) PIV'


    /************************************************************************************************
    ESEGUO LA PROCEDURA DYNAMIC PIVOT QUERY
    ************************************************************************************************/
    EXEC sp_executesql @DynamicPivotQuery

    DROP TABLE [dbo].[DETTAGLIO_COIN_REV] 
    SELECT * INTO [dbo].[DETTAGLIO_COIN_REV] 
    FROM #CoIn


    /************************************************************************************************
    ELIMINO LA TABELLA TEMPORANEA UTILIZZATA PER LA PIVOT DINAMICA DAL DB TEMP
    ************************************************************************************************/

    IF OBJECT_ID('tempdb.dbo.#CoIn', 'U') IS NOT NULL
    DROP TABLE #CoIn;

    grazie mille

    giovedì 11 febbraio 2021 17:00

Risposte

  • E' sufficiente aggiungere "INTO myPivot":

    /************************************************************************************************
    CREO LA PIVOT DINAMICA
    ************************************************************************************************/
    SET @DynamicPivotQuery = N'
    SELECT	* 
    INTO	myPivot
    FROM	#CoIn
    PIVOT	( SUM(VALORE) FOR COSTO_COD_DESCRIZIONE IN (' + @ColumnName + ') ) PIV';

    Dopo il run vedrai la nuova tabella myPivot popolata con tutti i valori della pivot.

    Il nome della tabella in cui persistere il risultato della pivot non deve essere lo stesso che già usi, #CoIn.

    G.

    venerdì 12 febbraio 2021 10:15

Tutte le risposte

  • Ciao,

    prova ad eseguire questa versione semplificata e verifica con print il contenuto delle due variabili utilizzate:

    drop table if exists #CoIn;
    declare @DynamicPivotQuery nvarchar(MAX), @ColumnName nvarchar(MAX);
    
    /************************************************************************************************
    POPOLO #CoIn
    ************************************************************************************************/
    
    select	ANNO,MESE,COSTO_COD_DESCRIZIONE,VALORE
    into	#CoIn
    from	[SgaM_Analisi].[dbo].[SMC_T2COIN_TAB_analisi] T0
    		left outer join [SgaM_Analisi].[dbo].[SMC_SHIPPINGRUN_analisi] T1                              
    on		(T0.CLIENTE_VIAGGIO_ANNO=T1.ORDINE_ANNO
    		and T0.CLIENTE_VIAGGIO_FILIALE=T1.ORDINE_FILIALE
    		and T0.CLIENTE_VIAGGIO_NUMERO=T1.ORDINE_NUMERO
    		and T0.CLIENTE_VIAGGIO_NUMERO_CARICO_SCARICO=T1.ORDINE_NUMERO_CARICO_SCARICO
    		and T0.VETTORE_VIAGGIO_ANNO=T1.VIAGGIO_ANNO
    		and T0.VETTORE_VIAGGIO_FILIALE=T1.VIAGGIO_FILIALE
    		and T0.VETTORE_VIAGGIO_NUMERO=T1.VIAGGIO_NUMERO)
    where	ANNO=2020 and MESE=11
    		and NUMERO_INTERNO in ('AG53TOR','AG73TOR');
    
    /************************************************************************************************
    SELEZIONO I VALORI DISTINTI PER LA COLONNA PIVOT
    ************************************************************************************************/
    select	@ColumnName= ISNULL(@ColumnName+N',', N'') + QUOTENAME(T.COSTO_COD_DESCRIZIONE)
    from	(select distinct COSTO_COD_DESCRIZIONE from #CoIn) as T 
    order	by T.COSTO_COD_DESCRIZIONE;
    
    /************************************************************************************************
    CREO LA PIVOT DINAMICA
    ************************************************************************************************/
    SET @DynamicPivotQuery = N'
    SELECT	* 
    FROM	#CoIn
    PIVOT	( SUM(VALORE) FOR COSTO_COD_DESCRIZIONE IN (' + @ColumnName + ') ) PIV';
    
    /************************************************************************************************
    ESEGUO LA PROCEDURA DYNAMIC PIVOT QUERY
    ************************************************************************************************/
    EXEC sp_executesql @DynamicPivotQuery;

    Nota che la variabile @PivotSelectColumnName non risulta utilizzata nel tuo codice: forse, a seconda del tuo dominio dati, devi usare questa invece della @ColumnName.

    Giorgio


    • Modificato gnic venerdì 12 febbraio 2021 06:15
    venerdì 12 febbraio 2021 06:15
  • ciao grazie mille, questa versione funziona perfettamente, ma resta sempre il mio problema di far eseguire la funzione pivot e popolare una tabella del db. Nel caso della mia query inserendo il comando into #CoIn creo una tabella temporanea, invece modificando la stringa in "into CoIn" mi crea una tabella fisica ma senza l'esecuzione della pivot. Dovrei creare una nuova tabella, dichiarando tutti i campi compresa la pivot, ma come faccio? 
    venerdì 12 febbraio 2021 08:27
  • E' sufficiente aggiungere "INTO myPivot":

    /************************************************************************************************
    CREO LA PIVOT DINAMICA
    ************************************************************************************************/
    SET @DynamicPivotQuery = N'
    SELECT	* 
    INTO	myPivot
    FROM	#CoIn
    PIVOT	( SUM(VALORE) FOR COSTO_COD_DESCRIZIONE IN (' + @ColumnName + ') ) PIV';

    Dopo il run vedrai la nuova tabella myPivot popolata con tutti i valori della pivot.

    Il nome della tabella in cui persistere il risultato della pivot non deve essere lo stesso che già usi, #CoIn.

    G.

    venerdì 12 febbraio 2021 10:15
  • ottimo, funziona. Grazie mille 
    venerdì 12 febbraio 2021 10:35