Re: pivot on sql

B_gd_n[ ]Sahlean — Wed, 19 Dec 2007 16:53:28 GMT

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.

pivot on sql

neo — Wed, 19 Dec 2007 15:37:57 GMT

salut

daca am nivelul de compatibilitate pe sql 2005 (80 in loc de 90 si nu il schimb ca nu trebuie) cum pot face sa lucrez cu pivot pe sql 2005 avand in vedere ca pivotul pe 2005 are ca cerinte nivel de compatibilitate :90?

merci

T-SQL

Re: pivot on sql

pivot on sql