Interogarile crosstab / pivot table (tabel pivot) pot fi clasificate SIMPLIST în:
a) interogari cu un numar fix variabil de coloane si
b) interogari un un numar variabil de coloane.
Pentru a fi clar modul de crearea a interogarilor crosstab se va utiliza urmatoarea tabela cu încasari:
CREATE TABLE Incasari(IDIncasare INT, Data DATETIME , Suma MONEY)
În aceasta tabela se adauga urmatoarele înregistrari de test pentru anul 2004:
INSERT INTO Incasari VALUES (1 ,'2004-01-10',100000)
INSERT INTO Incasari VALUES (2 ,'2004-01-15',50000)
INSERT INTO Incasari VALUES (3 ,'2004-01-20',60000)
INSERT INTO Incasari VALUES (5 ,'2004-02-10',110000)
INSERT INTO Incasari VALUES (6 ,'2004-02-15',40000)
INSERT INTO Incasari VALUES (7 ,'2004-02-20',45000)
INSERT INTO Incasari VALUES (9 ,'2004-03-10',55000)
INSERT INTO Incasari VALUES (10,'2004-03-15',65000)
INSERT INTO Incasari VALUES (12,'2004-03-20',75000)
si pentru anul 2005:
INSERT INTO Incasari VALUES (1 ,'2005-01-10',110000)
INSERT INTO Incasari VALUES (2 ,'2005-01-15',51000)
INSERT INTO Incasari VALUES (3 ,'2005-01-20',61000)
INSERT INTO Incasari VALUES (5 ,'2005-02-10',111000)
INSERT INTO Incasari VALUES (6 ,'2005-02-15',41000)
INSERT INTO Incasari VALUES (7 ,'2005-02-20',45100)
INSERT INTO Incasari VALUES (9 ,'2005-03-10',55100)
INSERT INTO Incasari VALUES (10,'2005-03-15',65100)
INSERT INTO Incasari VALUES (12,'2005-03-20',75100)
Se doreste afisarea încasarilor totale din fiecare an pe fiecare luna.
În cazul crearii unei interogari cu un numar fix de coloane (trei coloane) se va utiliza urmatoarea interogare SQL:
SELECT YEAR(Data) AS An,
SUM(CASE WHEN MONTH(Data)=1 THEN Suma ELSE 0 END) AS 'Col1',
SUM(CASE WHEN MONTH(Data)=2 THEN Suma ELSE 0 END) AS 'Col2',
SUM(CASE WHEN MONTH(Data)=3 THEN Suma ELSE 0 END) AS 'Col3'
FROM Incasari
GROUP BY YEAR(Data)
În acesta situatie se afiseaza pentru fiecare an pentru care s-au înregistrat încasari YEAR(Data) suma totala a încasarilor din primele trei luni (MONTH(Data)=1,2 sau 3) adica:
SUM(CASE WHEN MONTH(Data)=1 THEN Suma ELSE 0 END) samd.
Astfel, indiferent daca în baza de date sunt încasari din lunile 1,2,3,...12 interogarea va afisa totalul încasarilor din primele trei luni:
An Col1 Col2 Col3
----------- --------------------- --------------------- -----------
2004 210000.0000 195000.0000 195000.0000
2005 222000.0000 197100.0000 195300.0000
Pentru obtinerea unei tabele pivot cu un numar variabil de coloane trebuie sa se genereze dinamic o interogare SQL. Procedura urmatoare genereaza o astfel de interogare SQL.
De asemenea, solutia propusa în acest articol se poate folosi pentru cazurile în care valorile tip „Row Heading” (în acest caz anii: 2004, 2005) si cele de tip „Column Heading” (lunile din exemplul nostru: 1,2,3) sunt de tip INT (numere întregi).
Procedura stocata are urmatoarea structura (binenteles ca acesta procedura stocata poate fi modificata pentru a prelucra si alte tipuri de date):
CREATE PROCEDURE spCrossTab
@tabel AS NVARCHAR(255),
@linie AS NVARCHAR(255),
@coloana AS NVARCHAR(255),
@functie AS NVARCHAR(255),
@valoare AS NVARCHAR(255)
AS
CREATE TABLE #aux(Camp INT)
DECLARE @sql AS NVARCHAR(500), @camp AS INT, @sir_coloane AS NVARCHAR(500)
SET @sql = 'SELECT DISTINCT '+@coloana+' FROM '+@tabel+' ORDER BY '+@coloana
INSERT INTO #aux EXEC sp_executesql @sql
SET @sir_coloane = ''
DECLARE crs CURSOR FAST_FORWARD FOR SELECT Camp FROM #aux
OPEN crs
FETCH NEXT FROM crs INTO @camp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sir_coloane = @sir_coloane + ',' + @functie+'(CASE WHEN '+@coloana+'='+CAST(@camp AS NVARCHAR(25))+' THEN '+@valoare+' ELSE 0 END) AS [Col'+CAST(@camp AS NVARCHAR(25))+']'
FETCH NEXT FROM crs INTO @Camp
END
CLOSE crs
DEALLOCATE crs
SET @sql = 'SELECT '+@linie+' AS [Linie]'+@sir_coloane+' FROM '+@tabel+' GROUP BY '+@linie+' ORDER BY '+@linie
EXEC sp_executesql @sql
DROP TABLE #aux
Apelul procedurii astfel definite se poate realiza astfel:
EXEC spCrossTab 'Incasari','YEAR(Data)','MONTH(Data)','SUM','Suma'
rezultatele fiind aceleasi:
Linie Col1 Col2 Col3
----------- --------------------- --------------------- ---------------
2004 210000.0000 195000.0000 195000.0000
2005 222000.0000 197100.0000 195300.0000
Parametrii procedurii stocate au urmatoarele semnificatii:
- ‘Incasari’ : tabela sursa, poate fi si o lista de tabele conectate folosind operatorii JOIN,
- ‘YEAR(Data)’ : valorile care vor fi afisate în antetul fiecarei linii,
- ‘MONTH(Data)’ : valorile care vor constitui antetul fiecarei coloane,
- ‘SUM’ : functia folosita pentru agregarea datelor din urmatoarea coloana,
- ‘Suma’ : coloana folosita pentru agregarea datelor.