Forberedelse til SQL-intervju · leksjon

Leverandørspesifikk PIVOT- og krysstabellsyntaks

SQL Server PIVOT og Postgres crosstab, samt begrensningene deres.

Leksjon 2 av 413 trinn

Leverandørspesifikk PIVOT- og krysstabellsyntaks er en gratis leksjon i Forberedelse til SQL-intervju på CoddyKit. Dette er leksjon 2 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til SQL-intervju, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Utover betinget aggregering

Man kjenner allerede den portable CASE-pivotløsningen. Men intervjuere vil også vite om man kan bruke leverandørspesifikke pivot-operatorer når de er tilgjengelige.

SQL Server leveres med en egen PIVOT-operator. PostgreSQL tilbyr funksjonen crosstab i utvidelsen tablefunc. Kunnskap om begge, og om fallgruvene deres, viser erfaring fra virkelige prosjekter.

Oppbygningen av SQL Server PIVOT

SQL Servers PIVOT tar tre ting:

Et aggregat over verdikolonnen.
En FOR-setning som angir kolonnen hvis verdier skal bli nye kolonner.
En IN-liste med de bokstavelige verdiene som skal gjøres om til kolonner.

Den må brukes på en avledet tabell som eksponerer nøyaktig nøkkelen, spredningskolonnen og verdien – ikke noe mer.

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

Det implisitte GROUP BY

En subtil PIVOT-fallgruve som intervjuere tester: grupperingen er implisitt. SQL Server grupperer etter hver kolonne i kilden som IKKE er aggregeringskolonnen eller FOR-kolonnen.

Hvis den avledede tabellen ved et uhell også inneholder en ekstra kolonne som order_id, grupperer pivoten etter den også, og man får langt flere rader enn forventet. Begrens alltid den indre spørringen til nøkkel, spredning og verdi.

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

Kolonnenavn i hakeparenteser

I SQL Server er navnene på pivotkolonnene de bokstavelige verdiene fra dataene, omgitt av hakeparenteser. Hvis en verdi begynner med et siffer eller inneholder mellomrom, er hakeparenteser obligatoriske.

Man velger dem med det samme navnet i hakeparenteser i den ytre SELECT-setningen. Dette er også grunnen til at PIVOT ikke kan håndtere ukjente verdier uten dynamisk SQL: IN-listen er skrevet inn direkte i spørringen.

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

PostgreSQL crosstab

PostgreSQL har ikke nøkkelordet PIVOT. I stedet tilbyr utvidelsen tablefunc crosstab, en funksjon som tar en SQL-streng og omformer resultatet.

Utvidelsen må aktiveres først. crosstab forventer at kildespørringen returnerer nøyaktig tre kolonner: radidentifikator, kategori og verdi, i denne rekkefølgen.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

Kolonnedefinisjonslisten

Den mest feilutsatte delen av crosstab er den avsluttende kolonnedefinisjonslisten AS ct(...). Man må selv deklarere utdatakolonnenes navn og typer, og de må samsvare med antallet og rekkefølgen på kategoriene.

Hvis en kategori mangler for en rad, fyller crosstab den inn etter posisjon. Det kan forskyve dataene med mindre man bruker formen med to argumenter nedenfor.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

crosstab med to argumenter

For å unngå forskyvning når enkelte rader mangler noen kategorier, bruker man formen med to argumenter. Den andre spørringen returnerer den fullstendige, sorterte listen over kategoriverdier, slik at crosstab vet nøyaktig hvilken kolonne hver verdi hører til i.

Dette er den robuste formen intervjuere forventer når kategoriene er sparsomme.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQL har ingen av delene

Hvis intervjueren spør om MySQL, er svaret direkte: MySQL har verken PIVOT eller crosstab. Det eneste alternativet er betinget aggregering med CASE (eller kortformen SUM(... ) + IF()).

Det er nettopp derfor det portable CASE-mønsteret er så verdifullt: Det er den minste felles løsningen som fungerer overalt.

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

Praktisk eksempel: opptelling av statuser i SQL Server

Et rapporteringsbehov kan være: «én rad per region, med en kolonne som teller bestillinger i hver status». I SQL Server mates en begrenset avledet tabell inn i PIVOT med COUNT.

Fordi man teller selve statuskolonnen, telles hver statusrad som ikke er NULL i en gruppe. Den ytre SELECT-setningen lister hver status som en kolonne i hakeparenteser. Dette er det kortere alternativet til å skrive tre COUNT(CASE ...)-uttrykk.

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

Felles begrensninger

Både PIVOT og crosstab deler den samme grunnleggende begrensningen som betinget aggregering: utdatakolonnene må være kjent når spørringen skrives.

SQL Server: IN-listen er bokstavelig.
Postgres crosstab: kolonnedefinisjonslisten er bokstavelig.

Ingen av dem kan oppdage kategorier under kjøring. Det krever at SQL-strengen bygges dynamisk.

Hvilken bør man bruke?

Et godt intervjusvar sammenligner dem på en ærlig måte:

CASE-aggregering: portabel, lett å lese og fungerer i alle databasemotorer. Standardvalget.
SQL Server PIVOT: kortfattet for mange kolonner, men den implisitte grupperingen kan overraske.
Postgres crosstab: kraftig, men omstendelig; krever en utvidelse og en kolonnedefinisjonsliste.

Når man er i tvil, bør man velge betinget aggregering og nevne leverandøroperatorene som alternativer.

Kort kontroll

Få klarhet i SQL Server PIVOT-atferden som intervjuere undersøker.

Besvar den i den interaktive leksjonen

Oppsummering

Leverandørspesifikk pivotsyntaks på én skjerm:

SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])), med en implisitt GROUP BY over kolonner som blir igjen.
Postgres: crosstab() fra tablefunc, som krever en kolonnedefinisjonsliste; bruk formen med to argumenter for sparsomme data.
MySQL: ingen av delene finnes, bruk CASE.
Alle tre krever at kolonnene er kjent når spørringen skrives.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 30
Leksjoner: 120

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Leverandørspesifikk PIVOT- og krysstabellsyntaks» gratis?

Ja – hele teksten i «Leverandørspesifikk PIVOT- og krysstabellsyntaks» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til SQL-intervju-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Hva lærer jeg i «Leverandørspesifikk PIVOT- og krysstabellsyntaks»?

SQL Server PIVOT og Postgres crosstab, samt begrensningene deres. Du øver på Forberedelse til SQL-intervju med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til SQL-intervju?

Ingen tidligere erfaring er nødvendig. Forberedelse til SQL-intervju på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 2 av 4.

Hvor lang tid tar leksjonen «Leverandørspesifikk PIVOT- og krysstabellsyntaks»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til SQL-intervju-leksjonen?

Ja. Alle Forberedelse til SQL-intervju-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

Pivotering med betinget aggregering
Leverandørspesifikk PIVOT- og krysstabellsyntaks
Gjøre kolonner om til rader
Dynamiske pivottabeller med ukjente kolonner

← Tilbake til Forberedelse til SQL-intervju