DYNAMIC SQL PIVOT STATEMENT

Bu makalede t-sql ile pivot kullanımını göreceğiz. Fakat bunun öncesinde pivot table nedir ve ne amaçla kullanılır bunu bilmekte yarar var.

Hepimizin bildiği gibi bir tablo satırlardan ve sütünlardan oluşan veri kümesidir. Fakat yüzlerce satırdan oluşan bu verilerin kolay analiz edilebilmesi ve bir bakışta bize bir fikir verebilmesi için verileri grafiğe dökme veya bazı kriterlere göre gruplama gibi bazı işlemler yaparız.
Örneğin ay, yıl, adet bilgilerini içeren bir satış tablosunun yıl bazında toplam satış adedini vermesi için yıl bazında gruplanması gibi. Fakat yaptığımız bu gruplama bile bazen binlerce satırlık verinin analiz edilebilmesi için yeterli olmaz. Bu noktada satır sayısını azaltıp sütün saysını çoğaltmak gibi bir method izlenir. Raporlama konu başlığı altında Matris Rapor olarak geçen bu konu, Excel ve Databasede karşımıza Pivot Table olarak çıkmaktadır.

Kısacası Pivot Table bir tablodaki verileri yatay olarak göstermemizi sağlar, yani satırlarda bulunan bilgileri istediğimiz alanlara göre sutunlara çevirir.

Ben genellikle matris raporlarlamayı Reporting Service yardımı ile yaparım. Yazılımla uğraştığım ilk yıllarda matris rapor verebilme sorununu, code içerisinde döngüler sayesinde dinamik DataTable oluşturarak çözdüğüm zamanlarda olmuştu tabi :) Reporting Service pratik ve kullanışlı bir çözüm olsada bazı anlar bu raporlamayı direk Database tarafında yapmamızda gerekecektir kesinlikle.

Örnek üzerinden devam etmemiz gerekirse.

1. Aşağıdaki Query sayesinde Ay, Yil ve Adet bilgilerini içeren bir tablo oluşturalım.

CREATE TABLE Sales
(
      Yil         INT,
      Ay          NVARCHAR(10),
      Adet        INT
)

2. Tablomuza aşağıdaki verileri kaydedelim.

INSERT INTO Sales VALUES(2001, 'Ocak', 10)
INSERT INTO Sales VALUES(2001, 'Şubat', 40)
INSERT INTO Sales VALUES(2002, 'Ocak', 50)
INSERT INTO Sales VALUES(2002, 'Mart', 20)
INSERT INTO Sales VALUES(2003, 'Şubat', 35)
INSERT INTO Sales VALUES(2002, 'Şubat', 14)
INSERT INTO Sales VALUES(2003, 'Şubat', 33)
INSERT INTO Sales VALUES(2001, 'Ocak', 32)
INSERT INTO Sales VALUES(2003, 'Ocak', 10)
INSERT INTO Sales VALUES(2004, 'Ocak', 25)
INSERT INTO Sales VALUES(2004, 'Şubat', 15)
INSERT INTO Sales VALUES(2004, 'Mart', 11)
INSERT INTO Sales VALUES(2003, 'Mart', 55)
INSERT INTO Sales VALUES(2005, 'Şubat', 18)
INSERT INTO Sales VALUES(2001, 'Mart', 11)
INSERT INTO Sales VALUES(2005, 'Ocak', 20)
INSERT INTO Sales VALUES(2005, 'Mart', 13)

Eklediğimiz bu kayıtlar sayesinde tablomuzdan select yaptığımızda aşağıdaki kayıt kümesine erişiyor olacağız.

Yil
Ay
Adet
2001
Ocak
10
2001
Şubat
40
2002
Ocak
50
2002
Mart
20
2003
Şubat
35
2002
Şubat
14
2003
Şubat
33
2001
Ocak
32
2003
Ocak
10
2004
Ocak
25
2004
Şubat
15
2004
Mart
11
2003
Mart
55
2005
Şubat
18
2001
Mart
11
2005
Ocak
20
2005
Mart
13

Sadece tablomuz şu haliyle bile ay ve yil bazında analiz edilebilmesi oldukça zor bir durumda. Tabiki select sorgusunu Yil ve Ay kolonlarına göre gruplayarak toplam adeti bulabiliriz. Bu durumda ise karşımıza aşağıdaki gibi bir görüntü çıkacaktır.

Yil
Ay
ToplamAdet
2001
Mart
11
2002
Mart
20
2003
Mart
55
2004
Mart
11
2005
Mart
13
2001
Ocak
42
2002
Ocak
50
2003
Ocak
10
2004
Ocak
25
2005
Ocak
20
2001
Şubat
40
2002
Şubat
14
2003
Şubat
68
2004
Şubat
15
2005
Şubat
18

Bu işlem sonrasında ise toplam adedi ay ve yil bazında takip etmek daha kolaylaştı. Fakat bu haliyle dahi tablomuz bize ilk bakışta ay ve yıl bazında karşılaştırma yapabilme olanağını sağlamıyor tam anlamıyla.
Üstelik şuanda tablomuzdaki toplam veri sayısı sadece 17 :)

Şimdi birde tablomuzu matris olarak pivot yardımıyla görüntülemeye çalışalım.

3. Aşağıdaki sorguyu execute edelim.

SELECT      *
FROM       Sales
PIVOT
(
      SUM(Adet) FOR Ay IN([Ocak],[Şubat],[Mart])
) AS p

Bu durumda ise tablo görünümü aşağıdaki şekilde olacaktır.

Yil
Ocak
Şubat
Mart
2001
42
40
11
2002
50
14
20
2003
10
68
55
2004
25
15
11
2005
20
18
13

Sonuç olarak pivot yardımı ile elde ettiğimiz tablo görünümü, hem ayların kendi aralarında hemde yılların kendi aralarında karşılaştırılabilmesini kolaylaştırmış oldu. Bu yöntemle bu şekildeki matris raporlarını kod kısmında herhangi bir döngü kurmadan veya third part bir raporlama tool’u kullanmadan direk SQL tarafında oluşturmak mümkün :)

Fakat dikkat ederseniz kolonlarda yer alacak olan ayları biz sabit olarak query içerisinde yazdık. Bu örnek için olmasada bazen pivottaki kolonlarımızın tablodaki verilere bağlı olarak dinamik şekilde genişlemesi gerekebilir. Tabloda ay yerine Ürün bilgisi yer alsaydı, bu matris rapor sorgusunu tabloya eklenen her ürün için manual olarak güncellememiz gerekecekti.

Şimdi ise kolonların tablodan gelecek olan verilere bağlı olarak genişleyebileceği bir Query yazalım.

4. Aşağıdaki sorguyu yazarak Execute edelim.

DECLARE     @Tsql       NVARCHAR(MAX)
DECLARE     @Columns    NVARCHAR(MAX)
DECLARE     @Id         INT
DECLARE     @MaxId      INT
 
 
SELECT     DISTINCT Ay
INTO       #tmpColumns
FROM       Sales
 
 
SET         @Id         = 1
SET         @MaxId      = (SELECT COUNT(*) FROM #tmpColumns)
SET         @Columns    = ''
SET         @Tsql       = ''
 
SELECT     ROW_NUMBER() OVER(ORDER BY Ay) AS Sira,
           *
INTO       #tmpColumnsOrder
FROM       #tmpColumns
 
 
WHILE (@Id <= @MaxId)
BEGIN
 
      SET   @Columns    = @Columns +
                        (    SELECT      '['+Ay+']'
                             FROM        #tmpColumnsOrder
                             WHERE       Sira = @Id
                        )
 
      SET @Id           = @Id + 1
 
END
 
 
SET   @Columns = REPLACE(@Columns,'][','],[')
 
 
SET   @Tsql = '
 
      SELECT     *
      FROM       Sales
      PIVOT
      (
            SUM(Adet) FOR Ay IN (' + @Columns + ' )
      ) AS p
'
 
 
EXEC (@Tsql)
 
DROP TABLE #tmpColumns
DROP TABLE #tmpColumnsOrder

Bu T-SQL sayesinde ise tabloya diğer aylara ait satış bilgilerinin girilmesi durumunda herhangi bir güncelleme yapmamız gerekmeyecektir.

2 yorum listeleniyor...
Hazal Çıplak 09.06.2012 17:58
Elinize sağlık çok açıklayıcı olmuş.
Erbil Taner Tugaylı 17.10.2012 17:28
Teşekkürler...