Bu makale, SQL Server'da PIVOT ve UNPIVOT operatörlerinin kullanımına ilişkin eksiksiz bir genel bakış sunacaktır. PIVOT ve UNPIVOT operatörleri, aşağıdakilere izin veren ilişkisel operatörlere benzer: tablo değerli ifadeyi başka bir tabloya dönüştürme . Her iki operatör de büyük miktarda veriyi hızlı bir şekilde birleştirmeye ve karşılaştırmaya yardımcı olan çok boyutlu raporlama üretir.
Şunu kullanabiliriz: PİVOT operatörü tablo değerli ifadeleri dönüştürmemiz gerektiğinde. Bölüyor bir sütundan birçok sütuna benzersiz değerler nihai sonuçta. Aynı zamanda agregalar nihai sonuçta gereken kalan sütun değerleri. UNPIVOT operatörü tablo değerli bir ifadenin sütunlarındaki verileri, PIVOT'un tersi olan sütun değerlerine dönüştürür.
Aşağıda verilen basit diyagramın yardımıyla bunu anlayalım:
Bu şeklin sol tarafında şunu görüyoruz: orijinal veri kümesi üç sütunu olan: Yıl, Bölge, Ve Satış . Daha sonra sağ tarafta döndürülerek oluşturulan PIVOT tablosunu görebiliriz. Bölge (satırlar) Kuzey ve Güney'e (sütunlar) . Satırları sütunlara dönüştürdükten sonra şunları yapabiliriz: Satış sütunu değerlerinin toplamı PIVOT tablosunun sütunları ve satırları arasındaki her kesişim için.
Öncelikle isimli bir tablo oluşturalım. pivot_demo PIVOT ve UNPIVOT operatörlerini göstermek. Aşağıdaki ifade belirtilen veritabanımızda yeni bir tablo oluşturur:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Daha sonra bu tabloya aşağıdaki gibi bazı veriler ekleyin:
Java'da rastgele sayı
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
SELECT ifadesini kullanarak verileri doğrulayabiliriz. Aşağıdaki çıktıyı alacağız:
PİVOT Operatörü
Bu operatör tablo değerli ifadeleri döndürmek için kullanılır. İlk olarak SQL Server 2005 sürümünde tanıtıldı. Verileri satırlardan sütunlara dönüştürür. Bir sütundaki benzersiz değerleri birçok sütuna böler ve ardından nihai sonuçta gerekli olan kalan sütun değerlerini toplar.
PIVOT tablosu oluşturmak için aşağıdaki adımları izlemeliyiz:
- Özetleme için temel veri kümesini seçin.
- Türetilmiş bir tablonun veya CTE'nin (ortak tablo ifadesi) yardımıyla geçici sonuçlar oluşturun.
- PIVOT operatörünü kullanın.
Sözdizimi
Aşağıdaki sözdizimi, SQL Server'da PIVOT kullanımını gösterir:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Bu scripti kırarsak iki ayrı bölümden oluştuğunu görebiliriz. İlk bölüm ana tablodan verileri seçer, ikinci bölüm ise PIVOT tablosunun nasıl oluşturulacağını belirler. İkinci bölüm ayrıca SUM, FOR ve IN gibi bazı özel anahtar kelimeleri de içerir. Bu anahtar kelimelerin PIVOT operatöründeki anlamlarını görelim.
java'daki program
TOPLA
Bu operatör şunun için kullanılır: değerleri topla PIVOT tablosunda kullanılacak belirtilen sütundan. Değerler bölümleri için toplu sütun görünümlerini elde etmek için bunu PIVOT operatörüyle birlikte kullanmalıyız.
Anahtar Kelime İÇİN
Bu anahtar kelime PIVOT tablo ifadesi için kullanılır. PIVOT operatörüne talimat verin PIVOT fonksiyonunun hangi sütuna uygulanması gerektiği. Temel olarak satırlardan sütunlara dönüşecek sütun adlarını belirtir.
IN Anahtar Kelimesi
Bu anahtar kelime tüm benzersiz değerleri listeler PIVOT tablosunun sütunları olarak görüntülenecek PIVOT sütunundan.
Örnek
Çeşitli örnekler yardımıyla bunu anlayalım.
1. Aşağıdaki ifade, pivotlama için temel veri olarak ilk olarak Yıl, Kuzey ve Güney sütununu seçmektedir. Daha sonra türetilmiş tabloyu kullanarak geçici bir sonuç oluşturun ve son olarak nihai çıktıyı oluşturmak için PIVOT operatörünü uygulayın. Bu çıktı aynı zamanda artan yılda da sıralanır.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
Bu ifadenin çalıştırılması aşağıdaki çıktıyı üretecektir. Burada şunları görebiliriz: yıl değerlerine karşılık gelen Kuzey ve Güney bölgesi satışlarının hesaplanan toplamı .
2. Her yıl için bölge değerlerine karşılık gelen satışların toplamını hesaplayacağımız başka bir örnek:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
Bu bildirimin yürütülmesi hata üretmek çünkü sayısal değeri doğrudan sütun adı olarak belirtemeyiz.
Ancak SQL Server her tamsayı değerinden önce parantezleri kullanarak bu sorunu önlememize olanak sağlar. Güncellenen ifade aşağıdaki kod parçacığında gösterilmektedir:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Bu beyan başarıyla yürütülür ve bölge değerlerine karşılık gelen her yıl için hesaplanan satış toplamını görüntüler:
3. Bir önceki PIVOT tablosu alma örneği, tüm olası PIVOT sütunu değerlerinin farkında olduğumuzda faydalıdır. Ancak önümüzdeki yıl sütun sayısının arttığını varsayalım. Önceki örneği ele aldığımızda PIVOT sütunları olarak 2010, 2011 ve 2012 yıllarına sahibiz. Ancak bu sütunların gelecekte değişmeyeceğinin garantisi yoktur. Elimizde 2013 veya 2014 yılına ait, hatta daha fazlasına ait veriler varsa ne olur? Bu gibi durumlarda kullanmamız gerekecek dinamik PIVOT tablosu Bu sorunu çözmek için sorgular.
Dinamik PIVOT tablo sorgusu, PIVOT betiğinin tamamını saklı bir prosedürde kapsüller. Bu prosedür, birkaç parametreli değeri değiştirerek gereksinimlerimizi değiştirmemize olanak tanıyan ayarlanabilir seçenekler sunacaktır.
dizeyi jsonobject java'ya dönüştür
Aşağıdaki SQL kodu dinamik PIVOT tablosunun çalışmasını açıklamaktadır. Bu betikte, önce PIVOT sütunundaki tüm farklı değerleri aldık ve ardından çalışma zamanında PIVOT sorgusu ile yürütülecek bir SQL ifadesi yazdık. Bu betiği çalıştırdıktan sonra çıktıyı görelim:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
Bu komut dosyasında iki parametreli değişken oluşturduk. Açıklaması aşağıda verilmiştir:
@PivotColumn : Bu değişken sütunun adını PIVOT tablosunun oluşturulduğu orijinal tablodan alacaktır. Örneğin , burada 'Bölge' sütunu, sütunlarda bulunan tüm bölgeleri görüntüler.
@PivotList : Bu değişken PIVOT tablosunda çıktı sütunu olarak görüntülemek istediğimiz sütun listesini alacaktır.
boto3
Dinamik Saklı Prosedürün Yürütülmesi
Dinamik saklı yordamın başarılı bir şekilde oluşturulmasından sonra onu yürütmeye hazırız. Aşağıdaki ifade, çalışma zamanında PIVOT tablosunu görüntülemek amacıyla dinamik saklı yordamı çağırmak için kullanılır:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Burada artık sütun adını belirledik' Bölge ' ilk parametre olarak ve PIVOT sütun listesi ikinci parametre olarak. Komut dosyasının yürütülmesi aşağıdaki çıktıyı gösterecektir:
Artık PIVOT tablosunu görüntülemek için gelecekte çalışma zamanında daha fazla sütun ekleyebiliriz ki bu ilk iki örnekte mümkün değildir.
UNPIVOT Operatörü
SQL Server'da PIVOT operatörünün ters metodudur. Bu operatör şu işlemleri gerçekleştirir: PIVOT'un ters çalışması Verileri sütunlardan satırlara dönüştürerek. UNPIVOT operatörü ayrıca PIVOT tablosunu normal tabloya döndürür. İlk olarak SQL Server 2005 sürümünde tanıtıldı.
Sözdizimi
Aşağıdaki sözdizimi SQL Server'daki UNPIVOT'u göstermektedir:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Örnek
PIVOT işleminin nasıl UNPIVOT yapılacağını örneklerle anlayalım. İlk önce bir oluşturacağız orijinal tablo ve PIVOT tablo ve daha sonra bu tabloya UNPIVOT operatörünü uyguladık.
Aşağıdaki kod parçacığı ilk olarak geçici bir tablo değişkeni @Tab bildirir:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Daha sonra bu tabloya değerleri aşağıdaki gibi ekleyeceğiz:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Artık aşağıdaki ifadeyi kullanarak UNPIVOT işlemini gerçekleştirebiliriz:
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Kod pasajının çalıştırılması aşağıdaki çıktıyı döndürecektir:
Aşağıdaki kod parçacığı, tek bir sorguda aynı tablo üzerinde önce PIVOT işlemini, ardından UNPIVOT işlemini gerçekleştirmeye yönelik başka bir örnektir:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Kod pasajını çalıştırmak aynı çıktıyı gösterecektir:
android için mesaj oyunları
NOT: UNPIVOT işlemi, PIVOT prosedürünün ters işlemidir ancak tam tersi değildir. PIVOT toplamı hesapladığında satırlar birleştirilmiş olduğundan ve sonuçta birçok satır tek bir satırda birleştirildiğinden, UNPIVOT işlemi tabloyu orijinali gibi yapamaz. Ancak PIVOT operatörü birçok satırı tek bir satırda birleştirmezse, UNPIVOT operatörü orijinal tabloyu PIVOT çıkışından alabilir.
Çözüm
Bu makale, SQL Server'daki PIVOT ve UNPIVOT operatörlerine tam bir genel bakış sunacak ve bir tablo ifadesini diğerine dönüştürecektir. UNPIVOT'un PIVOT'un ters işlemi olduğu asla unutulmamalıdır, ancak PIVOT sonucunun tam tersi değildir.