8 Aralık 2011 Perşembe

SQL Pivot İşlemi

Tablolardaki satırları sütunlara çevirme işlemidir. Yani yatay olan bilgileri dikey olarak görüntüleriz. Örnek üzerinden ilerlersek daha anlaşılır olacağını düşünüyorum.

AdventureWorks veri tabanındaki SalesOrderDetail ve SalesOrderHeader tablolarını ele alalım. Bu tablolarda satışlar ile ilgili bilgiler yer almaktadır. Yıl ve ay bazında toplam satış tutarlarını gösteren bir sorgu yazalım.

SELECT YEAR(OrderDate) Yil,MONTH(OrderDate) Ay,SUM(LineTotal) Toplam FROM Sales.SalesOrderHeader SOH

JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID=SOD.SalesOrderID

GROUP BY YEAR(OrderDate),MONTH(OrderDate)

image

Yılın her ayı için bir satır elde ederek toplam satışları göstermiş olduk. Şimdi amacımız, yıl bilgileri satır olarak kalırken, ay bilgilerini kolon haline getirmek olsun.

SQL Server 2005’den önce aşağıdaki gibi bir yazım söz konusu idi.

SELECT YEAR(OrderDate),

SUM(CASE WHEN MONTH(OrderDate)=1 THEN LineTotal END) [Ocak],

SUM(CASE WHEN MONTH(OrderDate)=2 THEN LineTotal END) [Şubat],

SUM(CASE WHEN MONTH(OrderDate)=3 THEN LineTotal END) [Mart],

SUM(CASE WHEN MONTH(OrderDate)=4 THEN LineTotal END) [Nisan],

SUM(CASE WHEN MONTH(OrderDate)=5 THEN LineTotal END) [Mayıs],

SUM(CASE WHEN MONTH(OrderDate)=6 THEN LineTotal END) [Haziran],

SUM(CASE WHEN MONTH(OrderDate)=7 THEN LineTotal END) [Temmuz],

SUM(CASE WHEN MONTH(OrderDate)=8 THEN LineTotal END) [Ağustos],

SUM(CASE WHEN MONTH(OrderDate)=9 THEN LineTotal END) [Eylül],

SUM(CASE WHEN MONTH(OrderDate)=10 THEN LineTotal END) [Ekim],

SUM(CASE WHEN MONTH(OrderDate)=11 THEN LineTotal END) [Kasım],

SUM(CASE WHEN MONTH(OrderDate)=11 THEN LineTotal END) [Aralık]

FROM Sales.SalesOrderHeader SOH

JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID=SOD.SalesOrderID

GROUP BY YEAR(OrderDate)

ORDER BY 1

SQL Server 2005 ile gelen pivot anahtar sözcüğü ile,

SELECT Yil, [1][Ocak], [2][Şubat], [3][Mart], [4][Nisan], [5][Mayıs], [6][Haziran], [7][Temmuz], [8][Ağustos], [9][Eylül], [10][Ekim], [11][Kasım], [12][Aralık] FROM

(

SELECT YEAR(OrderDate) Yil,MONTH(OrderDate) Ay,SUM(LineTotal) Toplam FROM Sales.SalesOrderHeader SOH

JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID=SOD.SalesOrderID

GROUP BY YEAR(OrderDate),MONTH(OrderDate)

) [Tablo]

Pivot

(

      SUM(Toplam)

      FOR Ay IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])

) [PivotTablo]

ORDER BY Yil

Alacağımız ekran görüntüsü iki kod bloğu için de aynı olacaktır.

image



5 yorum:

Adsız dedi ki...

merhaba ,peki bu tablodaki null degerleri nasıl degistirebliriz yani eger null is sıfır(0) yazsın
tesekkürler

Adsız dedi ki...

COALESCE() sorunu çözecektir.

Adsız dedi ki...

ONUR bey merhaba çözemediğim bir sorun var; SQL pivot tabloda şu şekilde bir işlem yapmak istiyorum araştırdım ama internette detaylı bişeyler bulamadım
Aşağıdaki işlemde istediğim değerler giriş ve çıkış sutunlarına getirebiliyorum ama ben birde bu elde ettiğim iki sutunu birbirinde çıkararak saat farkını bulmak istiyorum

isim --------- giriş------- çıkış-------- fark???
Ahmet ------ 10:00------15:00--------?????





SELECT *FROM (SELECT isim,tarih,aciklama,saat,otel,departman FROM TblPuantor ) as gTablo

PIVOT(max(saat) FOR aciklama IN ([Giris],[Cikis])) AS p

Adsız dedi ki...

Merhaba,

Daha sonra burayı okuyacak arkadaşlar için null değerlerin 0 değeri ile değiştirilmesi işleminde ilk select cümleciğine "COALESCE" ya da "ISNULL" metodlarını eklemek sorunu çözecektir.

"SELECT Yil, COALESCE([1],0)[Ocak] ..." ya da "SELECT Yil, ISNULL([1],0)[Ocak]..." gibi.

Adsız dedi ki...

Merhaba,
Internette cok arastırdım ama boyle bır cvp bulamadım.
Asagıda sicilıd GirisTarih giris cikis surunları mevcut ben aynı ID'ye sahip sutunları aynı satırda gormek ıstıyorum bu pivotla mumkun ama yapamadım.Yardımcı olurmusunuz?
SicilID GirisTarih giris cikis
1 25-02-2016 07:27:07 12:22:19
1 25-02-2016 13:15:50 16:35:09

Yorum Gönder