Raporlar geliştiriyorsanız
veya açık şema bir ilişkisel veritabanı tasarlamanız gerekiyorsa, PIVOT ve UNPIVOT
operatörlerine ihtiyaç duymamış olamazsınız. Bu iki ifade görsel bir işlevi
yerine getirdiğinden, cümlelerle ne yaptıklarını anlatmak biraz sıkıcı gelebilir.
O nedenle,paragrafın takip eden kısmını sakin kafayla ve mümkünse kağıt üstünde
çizerek okumanızı tavsiye ederim. PIVOT ve UNPIVOT operatörleri dışarıdan bir
tablo değerini girdi olarak alırlar ve yeni bir tablo değeri oluştururlar. Bu
nedenle de FROM yan cümleciği ile birlikte kullanılırlar. PIVOT operatörü ile
satırlar, sütunlara dönüştürülür. Bunun için girdi tablo ifadeden, bir sütunu
pivot olarak belirlemek gerekir. Sorgu sonucu, bu sütunda yer alan değerlerden
her bir tekil değer için bir satır üretir ve diğer satırlar üstünde grupsal
bir işlem yapılarak aynı satırın sütunları olarak gösterilir. UNPIVOT ifadesi,
PIVOTun tam tersi olarak, sütunları belli bir pivot sütun kullanarak satırlara
ayırır. Bu nedenle bu iki ilişkisel operatör gruplamalı ifadelerle benzerlik
gösterir.
PIVOT
PIVOT operatörü
en çok açık şema uygulamalar ve OLAP türü sorgular için gereksinim duyulan bir
operatördür. Açık şema uygulama ile kastedilen şudur: örneğin bir e-ticaret
sitesi yaparken, kitaplar reyonu için önemli özellikler; sayfa sayısı, yazarı,
yayınevi iken; beyaz eşya için güç tüketimi, gürültü düzeyi gibi özellikleridir.
Bu durumda ürünler için sabit bir tablo ilişkisel veritabanı normalizasyon kuralları
çerçevesinde yeterli çözümü sağlamaz. Bu nedenle ayrı bir tabloda özellikler
satır olarak tutulup daha sonra bu özellikler üstünden sütunlar oluşturulabilir.
Örnek:
urunTest adında
ürünlerimizi tuttuğumuz bir tabloda çeşitli ürün bilgilerinin yer aldığını varsayalım.
CREATE TABLE
urunTest(
urunKod INT NOT NULL IDENTITY(1,1),
urunAd VARCHAR(255),
.....
) |
Aşağıdaki
şekide ürün özelliklerini tuttuğumuz bir de tbOzellik tablosu tasarlayarak ürünlerimizi
açık şema ile sunmak isteyelim.
CREATE TABLE
tbOzellik(
urunKod INT,
ozellik NVARCHAR(30),
deger SQL_VARIANT
) |
Özeliklerin
alacağı değerler bazen metin bazen de sayı temelli olabilir. Bu nedenle tabloyu
oluştururken, özellikerin değerlerini kaydetmek için SQL_VARIANT türünden bir
sütun oluşturduk.
İPUCU:
SQL_VARIANT, Visual Basicdeki VARIANT türüne benzer. Tipi, başlangıçta kesin
olarak belli olmayan veri türü için kullanılır. Veri girilirken SQL Sever tarafından
veya kullanıcı tarafından türü belirtilir. Böylece, farklı satırlarında farklı
tipten veriler saklayabilen bir sütun elde edilmiş olur.
Daha sonra şu kayıtları
girelim:
INSERT INTO
tbOzellik VALUES(1, NEbat, CAST(1.44 as DECIMAL));
INSERT INTO tbOzellik VALUES(2, NFormatlar, CAST(MPEG, DivX,DVD,VCD
as VARCHAR(20)));
INSERT INTO tbOzellik VALUES(2, NSes Çıkışı, CAST(5+1 as VARCHAR(6)));
INSERT INTO tbOzellik VALUES(3, NEkran, CAST(124*200 as VARCHAR(15)));
INSERT INTO tbOzellik VALUES(3,NKamera, CAST(800*600 as VARCHAR(15)));
INSERT INTO tbOzellik VALUES(4, NEkran, CAST(140*200 as VARCHAR(15)));
INSERT INTO tbOzellik VALUES(4,NKamera, CAST(800*600 as VARCHAR(15)));
|
…
Bizden şöyle bir
sonuç istendiğini varsayalım:
Her bir ürün için
bir satır olmak üzere, belli sayıda özeliklerden her birini bir sütun olarak
göstermemiz gerekiyor. İlgili sorguyu, PIVOT operatörünü kullanmadan şu şekilde
yazabiliriz:
SELECT U.urunAd,
MAX(CASE WHEN ozellik = SES ÇIKIŞI THEN deger END) AS [SES],
MAX(CASE WHEN ozellik = Ekran THEN deger END) AS [EKRAN],
MAX(CASE WHEN ozellik = Kamera THEN deger END) AS [KAMERA],
MAX(CASE WHEN ozellik = Formatlar THEN deger END) AS [FORMAT],
MAX(CASE WHEN ozellik = Ebat THEN deger END) AS [EBAT]
FROM tbOzellik O JOIN UrunTest U ON U.urunKod= O.UrunKod
WHERE O.urunKod BETWEEN 1 AND 4
GROUP BY u.UrunAd |
Şekil 1: PIVOT
operatörünün SQL Server 2000 ortamında gerçeklenmesi.(Query Analyzer)
PIVOT operatörünün
yaptığı aslında bu sorgudan çok farklı değildir. Ama daha sade bir sorgu için
kullanılabilir.
Örnek:
Bir önceki örnekte
elde ettiğimiz sonucu PIVOT kullanarak şu şekilde gerçekleştirebiliriz:
SELECT P .*
FROM tbOzellik
PIVOT
(
MAX ( deger )
FOR ozellik IN( [SES ÇIKIŞI] , [EKRAN] , [KAMERA] , [FORMATLAR] , [EBAT]
)
) AS P
WHERE urunKod BETWEEN 1 AND 4 |
Şekil 2: Aynı sonucun
PIVOT operatörü ile elde edilmesi
Şekil 3: SQL Server
2005 Beta-2 için yaptığım performans testinde, SQL Profilerdaki süre karşılaştırmaları,
PIVOT operatörünün şimdilik bir önceki yöntemle yazılmış sorguya göre ciddi
bir performans artışı sağlamadığını gösteriyor.(5 satır ve 27bin satırlık testler
her iki sorgu iç yaklaşık aynı sonuçları veriyor.)
PIVOT
Sorgu Parçası
|
Açıklama
|
Tablo_ifadesi
|
Üstünde
pivot sorgusunun çalıştırıldığı sanal tablo
|
Pivot_sutun
|
Sonuç
kümesinde üretilecek sütun listesini satırlarında barındıran sütun
|
Sutun_listesi
|
Pivot
sütunda yer alan değerlerden seçilmiş, sabit olarak verilmiş, sorgu sonucunda
üretilmesi uğruna PIVOT operatörünün çalıştırıldığı sütunlar listesi
|
Gruplamalı_fonksiyon
|
Verileri
gruplamada kullanılacak gruplamalı herhangi bir fonksiyon: MIN(), MAX()
veya UDAgg
|
Deger_sutunu
|
Her
bir sütunun altında gösterilecek değerleri barındıran sütun
|
Gruplama_sutunlari
|
Tablo_ifadesi
içerisinde yer alıp da, deger_sutunu ve pivot_sutunu
dışında kalan bütün sütunlar. Bu kısmın Sorguda açıkca görünmediğine
dikkat edin!
|
Secilecek_sutunlar
|
Türetilmiş
tabloyu da içine alan en baştaki geniş SELECT cümlesinin seçtiği sütunlar
|
Turetilmis_pivot_tablosu
|
PIVOT
işleminin sonucunu tutan ve içerisinde sutun_listesi ile Gruplama_sutunlari
nı içeren tablo bir tür türetilmiş tablodur.(örnekte P)
|
Tablo 1: Bir PIVOT
operatörünün bileşenlerini terim olarak bilmek, operatörü daha iyi kavramanızı
sağlayacaktır. (Bkz şekil 4 )
Şekil 4: PIVOT
işleminin teriminolojik anatomisinin bir sorgu üstünde gösterimi. Gruplama_sutunlari
normalde sorguda görünmeyen parçadır.
DİKKAT:
PIVOT operatöründen çıkan sütun listesindeki isimlerin mutlaka pivot sütunun
içerdiği değerlerden verilmiş olması gerekir. Pivot sütununda olmayan bir değer
PIVOT işlemi sütun listesinde yer alırsa, bu sütun için bütün satırlar NULL
olarak üretilecektir.
Bir önceki problemimize
geri dönersek, ürünlerin adını da yazdırmak için şu şekilde bir sorgu çalıştırdığımızı
düşünelim:
SELECT *
FROM urunTest U
INNER JOIN tbOzellik O
ON U.urunKod = O.urunKod
PIVOT(
MAX ( deger )
FOR ozellik IN( [SES ÇIKIŞI] , [EKRAN] , [KAMERA] , [FORMATLAR] , [EBAT]
)
) AS P
WHERE P.urunKod BETWEEN 1 AND 4 |
Bu sorgunun
SQL Server tarafından çalıştırılması mümkün değildir. Çünkü, PIVOT operatörü,
sadece ve sadece gruplama_sutunlari ve secilecek_sutunlar
dışında sütunu asla P türetilmiş tablosu içerisine döndürmez. Bir sorgu içerisinde
PIVOT operatörü varsa, sadece bu operatörün döndürdüğü türetilmiş tablo üstünde
yer alan sütunların seçilmesine müsaade eder.
Bu kadar kısıttan
sonra, PIVOT kullanarak ürünlerin özellikleri ayrı sütun olarak ve bir sütuda
da farklı tablodaki adlarını gösterecek bir sorgulamayı hiç yapamayacağız hissine
kapılmış olabilirsiniz. Ama korkmayın! bu türden bir durumda istediğimiz sonucu
daha önceden gördüğümüz CTEleri veya türetilmiş tabloları kullanarak gerçekleştirebiliriz.
CTE ile yapılabilen bir işlemi türetilmiş tablo ile de yapabiliyorsak, türetilmiş
tablo kullanılmasının daha sade bir sorgu çıkartacağını bildiğimize göre türetilmiş
tablo kullanarak problemin çözümü şu şekilde olacaktır:
SELECT U.urunAd,
PVT.*
FROM PVT
INNER JOIN urunTest U
ON U.urunKod = PVT.urunKod
(
SELECT urunKod
FROM tbOzellik O
PIVOT
(
MAX ( deger )
FOR ozellik
IN( [SES ÇIKIŞI] , [EKRAN] , [KAMERA] , [FORMATLAR] , [EBAT] )
) AS P
WHERE P.urunKod BETWEEN 1 AND 4
) AS PVT |
Sizce ürünlerle
özelikleri önce birleştirdikten sonra bir türetilmiş tablo oluşturup, daha sonra
bu tablo üstünden bir PIVOT işlemi yapılsa nasıl bir sonuç türetilirdi? Sorunun
doğru cevabı için, işlemcinizin başka bir iş ile meşgul olmadığı bir zamanda
öncelikle iki tablodan bir türetilmiş tablo üretip bu tablodan PIVOT yapan bir
sorguyu sonra da yukarıdaki sorguyu ayrı ayrı çalıştırarak, SQL Profilerdan
geçen süreyi kontrol edebilirsiniz. Ancak yukarıdaki sorgunun daha iyi performans
vermesi beklenir.
PIVOT operatörü
OLAP türü raporlamalarda, verileri özetlerken aylık, yıllık veya çeyrek dilimlik
özetlemeler yaparken de kullanılabilir.
Örnek:
Bayilerimizin bizden
aldığı siparişlerin miktarını ve tarihini tutan bir tablo şu şekildedir.
CREATE TABLE
tbSatisBilgisi(
musteri VARCHAR(25),
siparisTarihi DATETIME DEFAULT GETDATE(),
siparisMiktari MONEY
) |
ve bizden
yapılan alışverişlerin listesi şu şekilde verilmiş olsun:
INSERT INTO
tbSatisBilgisi VALUES(Ali,12.12.2004,1000)
INSERT INTO tbSatisBilgisi VALUES(Ali,13.11.2004,13000)
INSERT INTO tbSatisBilgisi VALUES(Ali,19.12.2005,1500)
INSERT INTO tbSatisBilgisi VALUES(Recep,12.12.2004,23000)
INSERT INTO tbSatisBilgisi VALUES(Recep,12.12.2005,1000)
INSERT INTO tbSatisBilgisi VALUES(Recep,12.12.2004,2000)
INSERT INTO tbSatisBilgisi VALUES(Şaban,12.12.2004,1123)
INSERT INTO tbSatisBilgisi VALUES(Şaban,12.12.2005,340)
INSERT INTO tbSatisBilgisi VALUES(Ramazan,12.12.2003,5000)
INSERT INTO tbSatisBilgisi VALUES(Ramazan,12.12.2004,12500)
INSERT INTO tbSatisBilgisi VALUES(Şevval,12.12.2004,5000) |
Daha sonra,
her bir müşterimizin 2003, 2004 ve 2005 yıllarında toplam ne kadarlık ciro yaptığını
bulacak bir sorgu şu şekilde yazılabilir:
SELECT Piv.*
FROM tbSatisBilgisi
PIVOT(
SUM(siparisMiktari)
FOR MUSTERI IN([2003],[2004],[2005])
) AS Piv |
UNPIVOT
İfadesi
Sütunlardan oluşan
bir tablo değeri, satırlar halinde döndürmek gerektiğinde kullanılan bir operatördür.
Örnek:
Bir önceki aşamada
oluşturduğumuz tabloyu öncelikle PIVOT edilmiş hali ile yeni bir tabloda tutalım.
Bunun için SELECT ... INTO ifadesini kullanacağız.
SELECT Piv.*
INTO tbPivotSatisBilgisi
FROM tbSatisBilgisi
PIVOT(
SUM(siparisMiktari)
FOR MUSTERI IN([2003],[2004],[2005])
) AS Piv |
Ardından
bu tablo üstünden bir UNPIVOT çalıştıralım:
SELECT
UP.*
FROM tbPivotSatisBilgisi
UNPIVOT(
siparisMiktari
FOR musteri IN([2003],[2004],[2005])
)
|
Sonuç
PIVOT ve UNPIVOT
özellikle açık şema uygulamalar geliştirirken, OLAP türü raporlamalar yaparken
rahatlatıcı etkiye sahiptir. SQL Server 2000 ve daha eski sürümlerde de CASE-WHEN
yapısı ile satırlardan sütun elde edilebilir. Kötü tarafıysa, sütun listesini
bir değişkenden alamamasıdır. Bu nedenle Stored procedure yazarak, bir cursor
içerisinde dinamik bir SQL ifadesini VARCHAR olarak üretip EXEC() fonksiyonu
ile çalıştırabilirsiniz. Ancak bu durumda da bir sorgu optimizasyonu yapılmayacaktır.
Makale:
Satırları Sütuna veya Sütunları Satıra Dönüştürmek ADO.NET ve SQL Yaşar Gözüdeli
|