PIVOT- en crosstab-syntaxis per databaseleverancier

Je kent de platformonafhankelijke draaitabel met CASE al. Maar interviewers willen ook weten of je leveranciersspecifieke operatoren voor draaitabellen kunt gebruiken wanneer die beschikbaar zijn.

SQL Server levert een speciale PIVOT-operator. PostgreSQL biedt een crosstab-functie in de tablefunc-extensie. Als je beide kent, inclusief hun valkuilen, laat je echte praktijkervaring zien.

SQL Server's PIVOT heeft drie onderdelen:

• Een aggregatiefunctie over de waardekolom.
• Een FOR-clausule die de kolom benoemt waarvan de waarden nieuwe kolommen worden.
• Een IN-lijst met de letterlijke waarden die in kolommen moeten worden omgezet.

De operator moet worden toegepast op een afgeleide tabel die precies de sleutel, de spreidingskolom en de waarde beschikbaar maakt, en niets meer.

Een subtiele valkuil van PIVOT waarop interviewers je toetsen, is dat de groepering impliciet is. SQL Server groepeert op elke kolom in de bron die NIET de geaggregeerde kolom of de FOR-kolom is.

Als je afgeleide tabel per ongeluk een extra kolom zoals order_id bevat, groepeert de draaitabel daar ook op en krijg je veel meer rijen dan verwacht. Beperk de binnenste query daarom altijd tot alleen sleutel, spreidingskolom en waarde.

In SQL Server zijn de namen van de gedraaide kolommen de letterlijke waarden uit de gegevens, omsloten door vierkante haken. Als een waarde met een cijfer begint of spaties bevat, zijn vierkante haken verplicht.

Je selecteert deze kolommen met dezelfde naam tussen vierkante haken in de buitenste SELECT. Daarom kan PIVOT ook niet zonder dynamische SQL overweg met onbekende waarden: de IN-lijst is hardgecodeerd.

PostgreSQL heeft geen sleutelwoord PIVOT. In plaats daarvan biedt de tablefunc-extensie de functie crosstab, die een SQL-tekenreeks aanneemt en de uitvoer ervan herschikt.

Je moet de extensie eerst inschakelen. crosstab verwacht dat de bronquery precies drie kolommen teruggeeft: rij-identificatie, categorie en waarde, in die volgorde.

Het foutgevoeligste onderdeel van crosstab is de afsluitende kolomdefinitielijst AS ct(...). Je moet de namen en typen van de uitvoerkolommen zelf opgeven. Deze moeten overeenkomen met het aantal en de volgorde van de categorieën.

Als een categorie voor een rij ontbreekt, vult crosstab de waarden op positie in. Daardoor kunnen gegevens verkeerd uitgelijnd raken, tenzij je de tweeargumentenvorm hieronder gebruikt.

Gebruik de tweeargumentenvorm om verkeerde uitlijning te voorkomen wanneer sommige rijen bepaalde categorieën missen. De tweede query geeft de volledige, geordende lijst met categoriewaarden terug, zodat crosstab precies weet in welke kolom elke waarde hoort.

Dit is de robuuste vorm die interviewers verwachten wanneer categorieën schaars zijn.

Als de interviewer naar MySQL vraagt, is het antwoord eenvoudig: MySQL heeft geen PIVOT en geen crosstab. Je enige optie is daar voorwaardelijke aggregatie met CASE (of de verkorte notatie SUM(... ) + IF()).

Precies daarom wordt het platformonafhankelijke CASE-patroon zo gewaardeerd: het is de gemene deler die overal werkt.

Een verzoek voor een rapport: "één rij per regio, met een kolom die de bestellingen per status telt." In SQL Server geef je een beperkte afgeleide tabel door aan PIVOT en gebruik je COUNT.

Omdat je de statuskolom zelf telt, wordt elke niet-NULL-statusrij in een groep meegeteld. De buitenste SELECT vermeldt elke status als kolom tussen vierkante haken. Dit is een beknopt alternatief voor het schrijven van drie expressies met COUNT(CASE ...).

Ook PIVOT en crosstab hebben dezelfde kernbeperking als voorwaardelijke aggregatie: de uitvoerkolommen moeten bekend zijn wanneer je de query schrijft.

• SQL Server: de IN-lijst is letterlijk.
• Postgres crosstab: de lijst met kolomdefinities is letterlijk.

Geen van beide kan tijdens de uitvoering categorieën ontdekken. Daarvoor moet je de SQL-tekenreeks dynamisch opbouwen.

Een goed antwoord in een sollicitatiegesprek vergelijkt ze eerlijk:

• Aggregatie met CASE: platformonafhankelijk, leesbaar en werkt in elke database-engine. De standaardkeuze.
• SQL Server PIVOT: beknopt bij veel kolommen, maar de impliciete groepering brengt mensen vaak in verwarring.
• Postgres crosstab: krachtig maar uitgebreid, en vereist een extensie en een lijst met kolomdefinities.

Kies bij twijfel voor voorwaardelijke aggregatie en noem de leveranciersoperatoren als alternatieven.

De syntaxis voor draaitabellen van leveranciers op één scherm:

• SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])), met een impliciete groepering over de overgebleven kolommen.
• Postgres: crosstab() uit tablefunc, met een lijst met kolomdefinities; gebruik de tweeargumentenvorm voor schaarse gegevens.
• MySQL: geen van beide bestaat, gebruik CASE.
• Bij alle drie moeten de kolommen bekend zijn wanneer je de query schrijft.