PIVOT ve UNPIVOT İşlemleri – Bölüm 44

Bazen verileri  kaydedildiği satır ve sütun düzeninden farklı bir düzende tutmak sonra da analiz etmek gerekebilmektedir. Örneğin bir satırdaki değerleri, sütunları kullanarak karşılaştırma yaparak verileri başka bir boyuttan görebilir ve daha kolay analiz yapabilirsiniz. Bu bölümde bu tür işlemlerin T-SQL PIVOT operatörüyle nasıl yapıldığını göreceğiz. Ayrıca UNPIVOT ifadesiyle de sütun düzeninden tekrar satır düzenine döndürmeyi öğreneceğiz.

PIVOT İşlemi Nedir?

SQL Server’da verileri satır yöneliminden sütun yönelimine döndürerek düzeni değiştirebilirsiniz. Bu işlemde sütundaki eşsiz değerler liste halinde birleştirilir ve ardından bu liste sütun başlıkları haline getirilir. Oluşturulan sütunlarda aggregate işlemi sonuçları tutulur.

Örneğin aşağıda aynı Category ve Orderyear değerlerine sahip satırlar görünmektedir. Bu satırlarda sadece Qty sütunundaki değerler değişiklik göstermektedir:

Category Qty Orderyear
--------------- ------ -----------
Dairy Products 12 2006
Grains/Cereals 10 2006
Dairy Products 5 2006
Seafood 2 2007
Confections 36 2007
Condiments 35 2007
Beverages 60 2007
Confections 55 2007
Condiments 16 2007
Produce 15 2007
Dairy Products 60 2007
Dairy Products 20 2007
Confections 24 2007
...
Condiments 2 2008
 (2155 row(s) affected) 

Bu verileri kategori ve yıla göre analiz etmek için Qty sütunundaki değerlerin toplamı hesaplanmış ve pivot işlemiyle tablo aşağıda gösterildiği gibi tekrar düzenlenmiştir:

Category 2006 2007 2008
-------------- ---- ---- ----
Beverages 1842 3996 3694
Condiments 962 2895 1441
Confections 1357 4137 2412
Dairy Products 2086 4374 2689
Grains/Cereals 549 2636 1377
Meat/Poultry 950 2189 1060
Produce 549 1583 858
Seafood 1286 3679 2716
 (8 row(s) affected) 

Yaptığımız pivot işlemi sonucunda her yılı bir sütuna dönüştürmüş olduk ve Qty sütunundaki değerleri kategori gruplandırması yaparak değerleri topladık ve işlemi tamamladık.

PIVOT Kavramları

Microsoft® SQL Server® 2005’te tanıtılan T-SQL PIVOT tablo operatörü, SELECT ifadesinde FROM’dan dönen output tablosu üzerinde çalışır. PIVOT’u kullanmak için şu üç şeyi sağladığınızdan emin olmanız gereklidir:

Gruplama: FROM ifadesinde input sütunlarını belirtmeniz gereklidir. Bu sütunlardan hangilerinin analiz edileceği belirlenir. Bu işlem, PIVOT işleminde kullanılmayan sütunlara dayanır.

Yayma: Pivot işlemi sonucunda oluşturulacak sütun isimlerini belirtmeniz gerekir.

Toplama (aggregation): Gruplandırılmış satırları işlemek için aggregation fonksiyonu (SUM vb.) belirlemeniz gereklidir.

Ayrıca, PIVOT operatörünün sonuç tablosuna takma ad vermeniz gerekir. Aşağıdaki örnekte mevcut ögeler gösterilmektedir.

Aşağıda Orderyear sütunu değerleri yayan sütun, Qty sütunu aggregate için ve Category sütunu da gruplama için kullanılmaktadır. Ayrıca Orderyear’daki değerlerle yeni sütun değerleri belirlenirken parantez kullanıldığını görüyoruz:

SELECT Category, [2006],[2007],[2008]
FROM ( SELECT Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D
 PIVOT(SUM(qty) FOR orderyear IN ([2006],[2007],[2008])) AS pvt; 

Not: Alt sorguda aggregate veya yayma işleminde kullanılmayan tüm sütunlarda gruplama gerçekleştirilir. Alt sorguya gereksiz sütun gönderilmemelidir.

Sorguda PIVOT kullanırken zorunda olduğumuz şeylerden biri ise sipariş yılı gibi yayma işlemi için sabit elemanlar sağlamak zorundayız. İlerleyen bölümlerde dinamik sorguları öğrendiğiniz zaman daha esnek PIVOT sorguları yazabileceksiniz.

UNPIVOT Nedir?

Unpivot işlemi mantıksal olarak pivot işleminin tersidir. Unpivot ile sütunlar satırlara dönüştürülür. Önceden düzeni değiştirilmiş (PIVOT veya başka yolla) verileri çekme işlemi sırasında satır düzenindeki tabloya döndürürken kullanılır. SQL Server’da bu işlem UNPIVOT ifadesiyle ile yapılır.

Verileri unpivot ederken satıra dönüştürülecek sütunlar belirlenir. Unpivot edilecek sütun sayısına bağlı olarak veriler bir veya daha fazla satırda döndürülür.

Aşağıda üç sütun unpivot edilmiştir. Her bir Orderyear değeri, Category değeri ile birleştirilerek oluşturulan yeni satıra aktarılmıştır. Ayrıca NULL değerler kaldırılmıştır, yani dönen sonuçta yer almazlar:

Category 2006 2007 2008
--------------- ---- ---- ----
Beverages 1842 3996 3694
Condiments 962 2895 1441
Confections 1357 4137 2412
Dairy Products 2086 374 2689
Grains/Cereals 549 2636 1377
Meat/Poultry 950 2189 1060
Produce 549 1583 858
Seafood 1286 3679 2716 

Category ve Orderyear’ın her kesişimiyle oluşturulan satırlar:

category qty orderyear
--------------- ---- ---------
Beverages 1842 2006
Beverages 3996 2007
Beverages 3694 2008
Condiments 962 2006
Condiments 2895 2007
Condiments 1441 2008
Confections 1357 2006
Confections 4137 2007
Confections 2412 2008 

Not: Unpivot işlemi sonucunda orijinal verilerle aynı sonuçlar elde edemeyiz. Pivotta aggregate işlemi sırasında verilerin ayrıntısı kaybedilir. Bu yüzden unpivot ile orijinal verileri %100 geri çekme imkanımız yoktur.

UNPIVOT işleminde şu üç şeyi sağlamanız gereklidir:

• Unpivot edilecek sütunlar.
• Unpivot edilen değerlerin konulacağı yeni sütuna bir isim.
• Unpivot edilmiş değerleri gösterecek sütun için bir isim.

Not: PIVOT işleminde olduğu gibi UNPIVOT’un sonucunu da türetilmiş tablo olarak tanımlayıp isimlendirmek gereklidir.

Aşağıda 2006, 2007 ve 2008 adlı sütunlar unpivot edilerek yeni sütunlar için category, qty ve orderyear isimleriyle oluşturulmuştur. (Bu teknik önceki sorguda örnek veri üretmek için kullanılmıştır.)

SELECT category, qty, orderyear
FROM Sales.PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2006],[2007],[2008])) AS unpvt; 

Dönen sonuçlar:

category qty orderyear
--------------- ----------- ---------
Beverages 1842 2006
Beverages 3996 2007
Beverages 3694 2008
Condiments 962 2006
Condiments 2895 2007
Condiments 1441 2008
Confections 1357 2006
Confections 4137 2007
Confections 2412 2008
Dairy Products 2086 2006
Dairy Products 4374 2007
Dairy Products 2689 2008 

Yeni bölümde görüşmek üzere.

Kaynak:
Using PIVOT and UNPIVOT