Pivot Ve Unpivot

1. PIVOT

Pivot, rows olarak adlandirilan satirlarda bulunan verileri sütunlara transfer etmemizi saglar. Daha cok gruplamak ya da ortalama degerlerler görüntülemek icin kullanilir. Pivot tablolar sayesinde detayli analiz yapabiliriz.

Kullanim yapisi;

select sütun1, sütun2 ...yada * tüm sütunlar icin

from(

select ...pivot sql sorgusu

) as tabloadi...takma ad yazabilirsiniz

pivot

(

min,max,....aggeration fk.

for[ sütun adlari ]

in([ sutun1 ],[ sutun2],..)

) as tabloadi...pivot tablo icin takma ad

order by...istege bali kullanilabilir

iki`ye ayrilir

Static Pivot

Dinamik Pivot

1.1. Static Pivot

Tabloda silinen , eklenen veya güncellenen veriye göre degismeyen Pivot yapisidir. Yani mesela yillara göre Urun `lerin kac adet satildigini veren bir pivot tabloda, tablonun sütunlarini her satilan ürünün satis yilina göre düzenlenmek yerine, sadece belirli tarihlere göre düzenlersek static bir pivot tavlo olusturmus oluruz. Ilerleyen sayfalarda verecegim örnekteki gibi berlili olan 1996,1997 ve 1998 yillari icin yazilan vir pivot tabloda 1999 yilinda ürün satilmissa gelmeyecek ya da bugün satilan bir ürün girildiginde 2015 yili pivot tabloma dahil olamayacak. Sadece önceden belirleyip yazdigimiz verilere göre kolonlar düzenlenecek.

Mesela Northwind`de

Örn1:

Sorgu :

Hangi urunlerin, hangi yillarda kacar tane satildigi bilgisine order details ,order ve products tablolarini joinliyerek ulasabiliyoruz.

Kodu:

select yil,UrunIsmi,sum(adet) as adet

from

(select count(p.ProductName) as adet,year(o.OrderDate) as yil, p.ProductName as UrunIsmi from [Order Details] od join Orders o on o.OrderID=od.OrderID join Products p on p.ProductID=od.ProductID

group by p.ProductName,o.OrderDate) as tablo

group by yil,UrunIsmi

order by UrunIsmi asc

aldigmiz sonuc yandaki gibi oluyor.

ürün ismi ile hangi yillarda kacar tane satildigina ulastik.

Pivot Sorgusu :

Fakat bunu nasil 1996,1997 ve 1998 yillarinda kacar adet hangi üründen satildigini veren bir result sete cevirirdik?

Pivot bunu yapmaya yarar. Satirda bulunan veriyi kolonlara transfer eder.

Pivot kodu:

select * from

(

select p.ProductName as UrunIsmi,year(o.OrderDate) as pivot_kolonum from [Order Details] od join Orders o on o.OrderID=od.OrderID join Products p on p.ProductID=od.ProductID

) as sorgu_tablom

pivot

(

count(pivot_kolonum) for pivot_kolonum in([1996],[1997],[1998])

) as pivot_tablom

order by UrunIsmi

Sonuc yandaki gibidir.

Bu sorgularda dikkat etmemiz gereken ise kesin deger aramiyoruz! yani aggeration function kullanacagimiz kolonlari ariyoruz.

Örn2:

Örnek olarak Employees tablosunda calisanlarin memleketlerine ulasilabiliyor hangi calisanin nereli oldugunu görebiliyoruz

'UK' ` dan 4 kisi 'USA' `dan ise 5 kisi var. Eger Memleketi kolonumdaki verileri ustte kolon olarak görmek isteseydik, Adi Soyadi kolonundaki verileri ise bu olusan yeni kolonlar altinda görmek isteseydik mümkün degildi. Cunku pivot kesin degerler icin kullanilmaz.

yanlis kod:

select * from

(

select FirstName +' '+ LastName as [Adi Soyadi], Country from Employees

) tablo1

pivot

(

[Adi Soyadi] for Country in ([USA],[UK])

) tablo2

Eger üstteki gibi pivot kodu yazmis olsaydik pivot( .... ) kismindaki koda dikkat edin [Adi Soyadi] kolonundaki verileri, Country kolonundaki USA ve UK verilerini kolon haline getirerek icine veri olarak ata.

Mantiken düsündügümüzde dahi USA` kolonunda 5 satirlik veri bulunacak UK kolonunda ise 4 satir.

En önemlisi ise direk olarak verinin kendisi ile analiz yapmak mümkün degil analiz gruplarin davranislarini incelediginden bu kod uygulanamaz.

Dogru kullanimi:

1.2. Dinamik Pivot

Dinamik pivot tablomuz icin tablolarda bulunan fakat her an degisebilecek ya da daha dogrusu güncelleme,silme veya eklenme islemine ugrayabilecek olan kolonlar üzerinde islem yapar. Yani dinamik pivot`da kolonlari olusturmak icin veri cekilen satirlar degistiginde kolonlarda degisir. Önce bu kolonlari bir isim altinda declare ile tanimlayarak olusturdugumuz pivot tablosu icinde kolon adlarini olusturdugumuz kisima yazariz. Verilerin kolona transform edilmesi durumunda kolonlar declare`a gelen verilere göre degistiginden dinamik halini alir.

select @kolonlarim=Coalesce(

@kolonlarim+ ',['+cast(year(OrderDate) as varchar)+']','['+cast(year(OrderDate) as varchar)+']'

)

üstteki kod da @kolonlarim icerisinde varchar bir ifade ile pivot tabloda kullanilacak sütunlar olusturuluyor. Sütunlari OrderDate tablosundan cektigi satirlara göre olustudugundan OrderDate de degisiklik oldugunda @kolonlarima da yansir.

ilk örnekteki statik sorgunun dinamik hali:

declare @kolonlarim varchar(max), @sorgu nvarchar(max)

select @kolonlarim=Coalesce(

@kolonlarim+ ',['+cast(year(OrderDate) as varchar)+']','['+cast(year(OrderDate) as varchar)+']'

)

from Orders

set @sorgu=

'select * from

(

select p.ProductName as UrunIsmi,year(o.OrderDate) as pivot_kolonum from [Order Details] od join Orders o on o.OrderID=od.OrderID join Products p on p.ProductID=od.ProductID

) as sorgu_tablom

pivot

(

count(pivot_kolonum) for pivot_kolonum in('+@kolonlarim+')

) as pivot_tablom

order by UrunIsmi'

2. UNPIVOT

Pivot isleminin tam tersini yapar yani sütunlari satirlara transafer eder. Kullanimi pivot ile ayni sadece pivot yerine unpivot yazmamiz gerekiyor.

Kullanim yapisi;

select sütun1, sütun2 ...yada * tüm sütunlar icin

from(

select ...unpivot sql sorgusu

) as tabloadi...takma ad yazabilirsiniz

unpivot

(

min,max,....aggeration fk.

for[ sütun adlari ]

in([ sutun1 ],[ sutun2],..)

) as tabloadi...unpivot tablo icin takma ad

order by...istege bali kullanilabilir

Cok sik kullanilan bir yapi degil cünkü; Tablolarda veriler cok nadir kategori adlari , ya da satis yillari tarzinda kolonlar altinda saklanir.

Unpivot yapilabilecek bir tablo olusturdum UrunAdet tablomun icerigi yandaki gibi

Unpivot Kodu :

select Pivot_tablom.*

from UrunAdet

unpivot

(

Adet for Yil in([1996],[1997],[1998])

) as Pivot_tablom

#Pivot #unpivot #dinamikpivot #statikpivot