Dynamiske kolonner i SELECT
Gjermund Omholt
Av og til ønsker man et uttrekk fra databasen hvor antall kolonner varierer avhengig av dataene i uttrekket. Et typisk eksempel er en rapport med mengde av noe fordelt per år:
| Produkt | 2006 | 2007 | 2008 | 2009 |
| Fox | 5 | 1 | 44 | 90 |
| Meller | 10 | 4 | 7 | 5 |
| Nox | 0 | 166 | 9 | 20 |
| Smash! | 16 | 12 | 0 | 15 |
Antall kolonner og hvilke årstall som er representert vil kunne variere avhengig av dataene. Denne artikkelen beskriver kort hvordan dette kan uttrykkes i SQL.
I eksempelet har vi en tabell (Produksjon) med følgende kolonner:
| Kolonnenavn | Datatype |
| ProduksjonId | int |
| Produkt | nvarchar(50) |
| Dato | datetime |
| Mengde | int |
Tabellen er laget for denne artikkelen og er ikke normalisert! J
Statisk SQL
Ved å benytte nøkkelordet CASE kan vi lage SQL for rapporten:
SELECT Produkt
,[2006] = SUM(CASE Year(Dato) WHEN 2006 THEN Mengde ELSE 0 END)
,[2007] = SUM(CASE Year(Dato) WHEN 2007 THEN Mengde ELSE 0 END)
,[2008] = SUM(CASE Year(Dato) WHEN 2008 THEN Mengde ELSE 0 END)
,[2009] = SUM(CASE Year(Dato) WHEN 2009 THEN Mengde ELSE 0 END)
FROM Produksjon
GROUP BY Produkt
Men dette uttrykket er statisk og rapporten vil alltid ha de samme kolonnene (årene 2006-2009) selv om datagrunnlaget over tid endrer seg og inneholder data fra andre år.
Dynamisk SQL
For å kunne lage rapporten dynamisk må SQL-uttrykket bygges opp basert på dataene:
DECLARE @Sql nvarchar(MAX)
SET @Sql = N’ SELECT Produkt’
SELECT @Sql = @Sql + N’
,[' + Aar + '] = SUM(CASE Year(Dato) WHEN ‘ + Aar + ‘ THEN Mengde ELSE 0 END)’
FROM (SELECT DISTINCT Aar = Convert(nchar(4), Year(Dato)) FROM Produksjon) a
SET @Sql = @Sql + N’
FROM Produksjon
GROUP BY Produkt’
–PRINT @Sql
EXEC sp_executesql @Sql
Her bygges en streng som inneholder statisk SQL som vist over. Forskjellen er at strengen bygges basert på dataene. Det er disse linjene som sørger for dynamikken:
SELECT @Sql = @Sql + N’
,[' + Aar + '] = SUM(CASE Year(Dato) WHEN ‘ + Aar + ‘ THEN Mengde ELSE 0 END)’
FROM (SELECT DISTINCT Aar = Convert(nchar(4), Year(Dato)) FROM Produksjon) a
Det genereres en liste over hvilke år som forekommer i tabellen og for hvert år legges det til en kolonne i SELECT-uttrykket.
PIVOT
Nøkkelordet PIVOT dekker omtrent samme funksjonalitet som vi har laget her med CASE. Dessverre gir ikke PIVOT noen god løsning for denne type rapporter. Lesbarheten er sterkt redusert og ytelsen er den samme. Her er PIVOT-basert SQL for samme rapport:
SELECT Produkt, [2006], [2007], [2008], [2009]
FROM (
SELECT Year(Dato) Aar, Mengde, Produkt
FROM Produksjon) p
PIVOT (
SUM(Mengde)
FOR Aar IN ([2006], [2007], [2008], [2009])) AS pvt
Litt mer om CASE
I eksemplet med statisk SQL kan det være andre rapporter som benytter andre funksjoner enn SUM, for eksempel å telle noe (COUNT). Pass da på at ELSE-verdien må være NULL ellers telles alle rader og ikke bare de som tilfredsstiller betingelsen i CASE. Eksempel:
SELECT Produkt
,[2006] = COUNT(CASE Year(Dato) WHEN 2006 THEN Mengde ELSE NULL END)
,[2007] = COUNT(CASE Year(Dato) WHEN 2007 THEN Mengde ELSE NULL END)
,[2008] = COUNT(CASE Year(Dato) WHEN 2008 THEN Mengde ELSE NULL END)
,[2009] = COUNT(CASE Year(Dato) WHEN 2009 THEN Mengde ELSE NULL END)
FROM Produksjon
GROUP BY Produkt
‘ELSE NULL’ er default og kan utelukkes.
Stikkord: SQL
