C#nedir?com
 
YAZAR HAKKINDA
Yaşar Gözüdeli
Yaşar Gözüdeli
http://www.csharpnedir.com/
İletişme geçmek için tıklayın.
10 Makalesi yayınlanmakta.
Yazar hakkında detaylı bilgi için tıklayın.
Yayınlanan diğer makaleleri için tıklayın.
İlgili etiketler: between farkli iliskisel insert kullanarak max(case musteri nedenle ozellik select server tbozellik tbsatisbilgisi unpivot uruntest ADO.NET/SQL Yaşar Gözüdeli
 
YAZI HAKKINDA
Türü : Makale
Serbest Köşede C#nedir?com üyelerinin hazırladıkları yazılar yayınlanır. Bu yazılar editör incelemesine girmeden yayınlanır.
Seviyesi : Orta
Kategori : ADO.NET/SQL
Yayınlanma Tarihi : 4.5.2005
Okunma Sayısı : 36309
Yorum Sayısı : 1     yorum yaz
Site İçi AramaSİTE İÇİ ARAMA
Üye Girişini AçÜye GİRİŞİ
Üye girişi için tıklayın.
Kullanıcı Adı
Şifre
 
Beni her zaman hatırla
Bir hafta boyunca kullanıcı bilgilerinizi kullanıcı çıkışı yapana kadar hatırlar. (Paylaşılan bilgisayarlarda önerilmez.)
 
Şifremi / Kullanıcı Adımı unuttum.
 
.net TV RSS Serbest KÖŞE (?)
Serbest Köşede C#nedir?com üyelerinin hazırladıkları yazılar yayınlanır. Bu yazılar editör incelemesine girmeden yayınlanır.
emre TAŞ
XML - Deniz Kılınç
emre TAŞ
yazının devamı >
emre TAŞ
Decompiling and Reverse Engineering .Net Radyo
emre TAŞ
yazının devamı >
emre TAŞ
Masaüstü mü ? İnternet Mi? .Net Radyo
emre TAŞ
yazının devamı >
emre TAŞ
.Net Kavramları - .Net Radyo
emre TAŞ
yazının devamı >
emre TAŞ
Yeni Başlayanlar için - .Net Radyo
emre TAŞ
yazının devamı >
Makale Gönder Bende Yazmak İstiyorum
.net TV RSSBlogroll
Turhal Temizer 'in Blogu
Conda install environment.yml Package 23.10.2019
Turhal Temizer 'in Blogu
Mac OS/X Removing CUDA 23.10.2019
Burak Selim Şenyurt
Bu Sefer Bir React Uygulamasını Heroku Üzerine Alalım 23.10.2019
Burak Selim Şenyurt
Azure SignalR Servisini Kullanmak 23.10.2019
  Diğer Herşey
Sponsorlar
BT Akademi
Medya Portakal
Video Hosting Sponsoru
Csharpnedir.com bir Ineta üyesidir
Uzman Abi
Her Yönüyle C# - Sefer Algan
Satırları Sütuna veya Sütunları Satıra Dönüştürmek
 
Kapat
Sayfayı Yazdır Sık Kullanılanlara Ekle Arkadaşıma Gönder MySpace Del.Ico.Us Digg Facebook Google Mixx Reddit StumbleUpon
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, PIVOT’un 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 Basic’deki 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, N’Ebat’, CAST(1.44 as DECIMAL));
INSERT INTO tbOzellik VALUES(2, N’Formatlar’, CAST(’MPEG, DivX,DVD,VCD’ as VARCHAR(20)));
INSERT INTO tbOzellik VALUES(2, N’Ses Çıkışı’, CAST(’5+1’ as VARCHAR(6)));
INSERT INTO tbOzellik VALUES(3, N’Ekran’, CAST(’124*200’ as VARCHAR(15)));
INSERT INTO tbOzellik VALUES(3,N’Kamera’, CAST(’800*600’ as VARCHAR(15)));
INSERT INTO tbOzellik VALUES(4, N’Ekran’, CAST(’140*200’ as VARCHAR(15)));
INSERT INTO tbOzellik VALUES(4,N’Kamera’, 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 Profiler’daki 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 CTE’leri 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 Profiler’dan 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
  • Yazılan Yorumlar
  • Yorum Yaz
ARA
22
2005
Gerçi makale 2005 fonksiyonları hakkında ama beni asıl etkileyen 2000de kullanabileceğim aşağıdaki kısım oldu: 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 Bu max vs hilesi bilmiyorum çok bilinen birşey mi ama ben ilk defa burada gördüm, ve beni bir dolu kod yazmaktan kurtardı.
Sayfalar : 1 
Yorum yazabilmek için üye girişi yapmalısınız. Üye girişi için tıklayın.
Üye değilseniz Üyel Ol linkine tıklayarak üyeliğinizi hemen başlatabilirisniz.
 
  • Bu Konuda Son 10
  • Eklenen Son 10
  • Bu Konuda Geçmiş 10
Bu Konuda Yazılmış Yazılmış 10 Makale Yükleniyor
Son Eklenen 10 Makale Yükleniyor
Bu Konuda Yazılmış Geçmiş Makaleler Yükleniyor