Sintassi PIVOT e crosstab specifica del database

Conoscete già il pivot portabile con CASE. Ma gli intervistatori vogliono anche sapere se siete in grado di usare gli operatori di pivot specifici del database quando sono disponibili.

SQL Server include l'operatore dedicato PIVOT. PostgreSQL offre la funzione crosstab nell'estensione tablefunc. Conoscere entrambi, insieme ai loro punti critici, dimostra esperienza reale.

Il PIVOT di SQL Server richiede tre elementi:

• Un'aggregazione sulla colonna dei valori.
• Una clausola FOR che indica la colonna i cui valori diventeranno nuove colonne.
• Un elenco IN dei valori letterali da trasformare in colonne.

Deve essere applicato a una tabella derivata che esponga esattamente la chiave, la colonna di distribuzione e il valore, senza nient'altro.

Un'insidia sottile di PIVOT che gli intervistatori verificano è il raggruppamento implicito. SQL Server raggruppa per ogni colonna della sorgente che NON è la colonna aggregata né la colonna FOR.

Quindi, se la tabella derivata include accidentalmente una colonna aggiuntiva come order_id, anche quella verrà usata per il raggruppamento e otterrete molte più righe del previsto. Riducete sempre la query interna alla sola chiave, colonna di distribuzione e valore.

In SQL Server i nomi delle colonne sottoposte a pivot sono i valori letterali dei dati, racchiusi tra parentesi quadre. Se un valore inizia con una cifra o contiene spazi, le parentesi quadre sono obbligatorie.

Nella SELECT esterna dovete selezionarli usando lo stesso nome tra parentesi quadre. Questo spiega anche perché PIVOT non può gestire valori sconosciuti senza SQL dinamico: l'elenco IN è codificato esplicitamente.

PostgreSQL non dispone della parola chiave PIVOT. L'estensione tablefunc fornisce invece crosstab, una funzione che accetta una stringa SQL e riorganizza il relativo output.

Dovete prima abilitare l'estensione. crosstab richiede che la query sorgente restituisca esattamente tre colonne, in quest'ordine: identificatore di riga, categoria e valore.

La parte più soggetta a errori di crosstab è l'elenco di definizione delle colonne finale AS ct(...). Dovete dichiarare personalmente i nomi e i tipi delle colonne di output, che devono corrispondere al numero e all'ordine delle categorie.

Se manca una categoria per una riga, crosstab la inserisce in base alla posizione; ciò può disallineare i dati, a meno che non usiate la forma a due argomenti riportata di seguito.

Per evitare disallineamenti quando alcune righe non contengono determinate categorie, usate la forma a due argomenti. La seconda query restituisce l'elenco completo e ordinato dei valori delle categorie, così crosstab sa esattamente in quale colonna inserire ogni valore.

Questa è la forma robusta che gli intervistatori si aspettano quando le categorie sono sparse.

Se l'intervistatore vi chiede di MySQL, la risposta è diretta: MySQL non dispone né di PIVOT né di crosstab. L'unica opzione è l'aggregazione condizionale con CASE (oppure la forma abbreviata SUM(... ) + IF()).

È proprio per questo che il pattern portabile con CASE è così apprezzato: è il minimo comune denominatore che funziona ovunque.

Una richiesta di report: "una riga per regione, con una colonna che conti gli ordini per ogni stato". In SQL Server, fornite a PIVOT una tabella derivata ridotta usando COUNT.

Poiché contate direttamente la colonna dello stato, viene conteggiata ogni riga con stato non NULL in un gruppo. La SELECT esterna elenca ogni stato come colonna tra parentesi quadre. È l'alternativa concisa alla scrittura di tre espressioni COUNT(CASE ...).

Sia PIVOT sia crosstab condividono lo stesso limite fondamentale dell'aggregazione condizionale: le colonne di output devono essere note quando scrivete la query.

• SQL Server: l'elenco IN è letterale.
• crosstab di Postgres: l'elenco di definizione delle colonne è letterale.

Nessuno dei due può scoprire le categorie a runtime. Per farlo è necessario costruire dinamicamente la stringa SQL.

Una buona risposta a un colloquio li confronta con obiettività:

• Aggregazione con CASE: portabile, leggibile e compatibile con ogni motore. È la scelta predefinita.
• PIVOT di SQL Server: conciso per molte colonne, ma il raggruppamento implicito può sorprendere.
• crosstab di Postgres: potente ma prolisso; richiede un'estensione e un elenco di definizione delle colonne.

In caso di dubbio, scegliete l'aggregazione condizionale e menzionate gli operatori del database come alternative.

Precisate il comportamento di SQL Server PIVOT che gli intervistatori verificano.

La sintassi dei pivot specifici del database in un'unica schermata:

• SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])), con un GROUP BY implicito sulle colonne rimanenti.
• Postgres: crosstab() da tablefunc, che richiede un elenco di definizione delle colonne; usate la forma a due argomenti per i dati sparsi.
• MySQL: nessuno dei due è disponibile, usate CASE.
• Per tutti e tre le colonne devono essere note al momento della scrittura della query.