SQL Server : GROUP_CONCAT ve LISTAGG Simülasyonu
Zaman zaman, birden çok satırın konusu olan bazı verileri tek hücrede göstermek istediğimiz durumlar olabiliyor, ( Örneğin, bir tabloda geçen alanların adlarını hemen yanındaki sütunda virgüllerle ayrılmış şekilde görmek gibi )
MySQL’de bunun için harika bir komut var, tam da bu işi yapıyor GROUP_CONCAT, aynı şekilde Oracle ve DB2’de de aynı işi yapan LISTAGG fonksiyonu var fakat SQL Server’de bu işi yapmanın doğrudan bir yolu yok, yani bir fonksiyona indirgenmiş hazır bir çözüm bulunmuyor. Bir bakıma hazır bir çözümün olmaması bizler için bir esneklik olarak da görülebilir, zira insanın ufkunu açan kısım da tam olarak burası çünkü önünüzde sizi engelleyecek bir durum yok, dolayısıyla herşeyi denememeniz için hiç bir neden göremiyorum.
( Başlamadan önce örneklerimizi bir tablo değişkeni üzerinde göstereceğiz ve temp tablolar kullanarak diskinizi yormayacağız, böylece SQL Server’da gereksiz ve sonradan silmek isteyeceğiniz türden çöp tabloların oluşmasına neden olmayacağız, bu sayfadaki kodları belleşte çalışacağı, işin içine disk girmeyeceği için doğrudan kopyala-yapıştır yöntemiyle deneyebilirsiniz. )
Şimdiki makalemizde bu işlevselliği sorgularımızda nasıl sağlayabiliriz ve bunu hangi farklı yollarla gerçekleştirebiliriz onu irdeleyeceğiz.
Deneysel verilerimizi bir tablo değişkeni oluşturarak temelimizi atmış olalım;
DECLARE @VERI TABLE
( ID INT IDENTITY(1,1)
, Ad VARCHAR(20)
, Soyad VARCHAR(20)
);
INSERT INTO @VERI (Soyad, Ad) VALUES ('Kurt', 'ali'), ('kurt', 'veli'), ('KURT', 'nuri'), ('ARSLAN', 'ali'), ('ARSLAN', 'hamdi'), ('ARSLAN', 'vecidi'), ('ARSLAN', 'Göksel'), ('Kaplan', 'muhammet'), ('Kaplan', 'Ali'), ('Kaplan', 'Şahin'), ('Kaplan', 'Aslan');
SELECT ID, Ad, Soyad FROM @VERI;Şöyle bir tablo elde etmiş olduk
ID Ad Soyad
----------- -------------------- --------------------
1 ali Kurt
2 veli kurt
3 nuri KURT
4 ali ARSLAN
5 hamdi ARSLAN
6 vecidi ARSLAN
7 Göksel ARSLAN
8 muhammet Kaplan
9 Ali Kaplan
10 Şahin Kaplan
11 Aslan Kaplan
(11 row(s) affected)bu örnekteki amacımız aynı soyadına sahip olan kişileri soyadına göre gruplayıp isimlerini virgül ile ayrılmış bir şekilde tek bir sütunda göstermek olacak. Yani sonuç olarak aşağıdaki gibi bir liste elde etmemiz gerekiyor;
Soyad Ailedekiler
-------------------- ---------------------------------
ARSLAN ali, Göksel, hamdi, vecidi
Kaplan Ali, Aslan, muhammet, Şahin
Kurt ali, nuri, veli
(3 row(s) affected)Aşağıdaki gibi bir kod kullanarak bu amaca ulaşabiliriz, fakat bu kod SQL Server’in veritabanı motoru açısından çok yorucudur, yani gereksiz fazladan işler yapar, örnek olması açısından güzel fakat “kullanırmısın” dersen, kullanmam derim. Bu arada bu tekniğin adı “Recursive CTE” olarak bilinmektedir…
WITH
SIRALI as (
SELECT Soyad
, Ad
, Siralama = ROW_NUMBER() OVER (PARTITION BY Soyad ORDER BY Ad) -- Bu noktada her bir satıra kendi soyadı grubu içinde bir sıra numarası vermiş oluyoruz
FROM @VERI --> AS SIRALI anlamındadır...
)
, SONUC AS (
SELECT SIRALI.Soyad
, cast(SIRALI.Ad as varchar(max)) as [İsimler] /* Köşeli parantezdeyken Arapça alan adları bile kullanabilirsiniz */
, SIRALI.Siralama
FROM SIRALI --> AS SONUC anlamındadır...
WHERE SIRALI.Siralama = 1
UNION ALL
SELECT SIRALI.Soyad
, SONUC.[İsimler] + ', ' + SIRALI.Ad
, SIRALI.Siralama
FROM SIRALI
INNER JOIN SONUC ON SONUC.Soyad = SIRALI.Soyad
AND SIRALI.Siralama = SONUC.Siralama + 1
)
SELECT Soyad, MAX([İsimler]) as Ailedekiler
FROM SONUC
GROUP BY Soyad
ORDER BY Soyad
OPTION (MAXRECURSION 0);Yukarıda, pek de sevmediğim ama örnek olması açısından bulunsun niyetiyle yazdığım örnek kodu bir kenara bırakırsak aynı işlevselliği bir İMLEÇ kullanarak da sorgularımıza kazandırabiliriz, fakat haliyle bu da olabildiğince karmaşık bir sorgu olacaktır haliyle, ki karmaşıklığı kimse sevmez…
DECLARE @VERI TABLE
( ID INT IDENTITY(1,1)
, Ad VARCHAR(MAX) --> Önceki örneklerde MAX yerine 20 bayt olduğunu belirtmiştik, şimdiki örnekte bu alana ailedeki isimleri toplayacağımız için böyle bir değişiklik yaptık...
, Soyad VARCHAR(20)
);
INSERT INTO @VERI (Soyad, Ad) VALUES ('Kurt', 'ali'), ('kurt', 'veli'), ('KURT', 'nuri'), ('ARSLAN', 'ali'), ('ARSLAN', 'hamdi'), ('ARSLAN', 'vecidi'), ('ARSLAN', 'Göksel'), ('Kaplan', 'muhammet'), ('Kaplan', 'Ali'), ('Kaplan', 'Şahin'), ('Kaplan', 'Aslan');
DECLARE @Soyad VARCHAR(MAX)
, @Ad VARCHAR(MAX)
, @Ailedekiler VARCHAR(MAX)
;
DECLARE imlec CURSOR LOCAL FAST_FORWARD
FOR SELECT Soyad, Ad
FROM @VERI
ORDER BY Soyad, Ad
;
OPEN imlec;
FETCH imlec INTO @Soyad, @Ad;
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE @VERI
SET Ad = Ad + ', ' + @Ad
WHERE Soyad = @Soyad
;
FETCH imlec
INTO @Soyad, @Ad;
END
CLOSE imlec;
DEALLOCATE imlec;
SELECT Soyad, MAX(Ad) as Ailedekiler
FROM @VERI
GROUP BY Soyad
ORDER BY Soyad;İmleç kullanmak mecbur olmadığınız sürece kaçmanız / kaçınmanız gereken ekstrem bir durumdur, veritabanı motoruna satır bazında yük bindirir ve sonuç almak böyle kısa veri yığınları için dert olmasa bile binlerce verinin olduğu bir tabloda kelimenin tam anlamıyla sizi süründürür. Mümkünse uzak durum (ama yöntemi bilin…)
Bunun dışında örnekleri biraz daha radikalleştirip bu iş için UPDATE komutunu kullanabilir ve Ailedekileri ayrı bir sütuna hesaplatarak yazabiliriz, çalıştığımız tablo bir cari hesap ekstresi olsaydı kesinlikle bu yöntemi önerirdim ama verileri inceleyen 3. gözler bu durumu pek hoş karşılamazdı, yine de paylaşayım;
QUIRKY UPDATE olarak isimlendirilen bu tekniği uygulamadan önce tablo yapısında fazladan bir alana daha ihtiyacımız olacak, tam kod şöyle;
DECLARE @VERI TABLE
( ID INT IDENTITY(1,1)
, Ad VARCHAR(20)
, Soyad VARCHAR(20)
, Ailedekiler VARCHAR(MAX)
);
INSERT INTO @VERI (Soyad, Ad) VALUES ('Kurt', 'ali'), ('kurt', 'veli'), ('KURT', 'nuri'), ('ARSLAN', 'ali'), ('ARSLAN', 'hamdi'), ('ARSLAN', 'vecidi'), ('ARSLAN', 'Göksel'), ('Kaplan', 'muhammet'), ('Kaplan', 'Ali'), ('Kaplan', 'Şahin'), ('Kaplan', 'Aslan');
DECLARE @Soyad VARCHAR(20), @Ailedekiler VARCHAR(MAX);
UPDATE @VERI
SET @Ailedekiler = Ailedekiler = COALESCE( CASE COALESCE(@Soyad, '') WHEN Soyad THEN @Ailedekiler + ', ' + Ad ELSE Ad END, '')
, @Soyad = Soyad
;
SELECT Soyad
, MAX(Ailedekiler) as Ailedekiler
FROM @VERI
GROUP BY Soyad
ORDER BY Soyad;Benim daha çok tercih ettiğim ve size önereceğim ise XML PATH yöntemidir, bununla birlikte yazının devamında CROSS APPLY tekniği de favorilerim arasında….
SELECT DISTINCT
Soyad
, STUFF ( ( SELECT ', ' + AAA.Ad
FROM @VERI as AAA
WHERE AAA.Soyad = SONUC.Soyad
ORDER BY AAA.Ad ASC
FOR XML PATH('')
)
, 1
, 2
, '') as Ailedekiler
FROM @VERI as SONUC;Görüldüğü gibi kısa, öz ve basit bir teknik olmasına mukabil buna (inline sql olmasına) rağmen “hızlıdır”
Şayet, SQL Server 2014 ve sonrasını kullanıyorsanız CROSS APPLY tüm bunların hepsinden daha iyi bir seçenek olabilir, hem basit, hem hızlı, hemde mantığını kurmak programcılar açısından daha basittir, işte örnek kodlar;
SELECT DISTINCT
Soyad
, STUFF(Sulaledekiler, 1, 2, '') as Ailedekiler
FROM @VERI as SSS
CROSS APPLY
( SELECT concat(', ', Ad)
FROM @VERI as AAA
WHERE AAA.Soyad = SSS.Soyad
ORDER BY AAA.Ad ASC
FOR XML PATH('')
) as tmp (Sulaledekiler)
;Performans konusuna gelecek olursak , ben testlerimi SQL Server 2012 üzerinde yaptım ve edindiğim sonuçlar 20000 satırlı bir tablo için milisaniye cinsinden şöyle; (Makinanızın işlemci gücü ve hızı herkeste değişik olduğundan siz farklı ama paralel sonuçlar elde edebilirsiniz)
CROSS APPLY 96,77 ms
XML Path 98,47 ms
QUIRKLY Update 267,72 ms
RECURSIVE CTE 486,33 ms
CURSOR 2198,51 msSONUÇ
- Eğer SQL Server 2000 gibi çok eski bir sürümde sıkışıp kaldıysanız muhtemelen bu iş için her tabloya özel olak üzere bir scalar fonksiyon yazmanız gerekecektir ki muhtemelen performansı yine de bir imleç kullanmaktan daha iyi olacaktır.
- 2005, 2008, 2012 gibi sürümlerden birini kullanıyorsanız XML PATH yöntemi en iyi çözüm olarak önümüzde duruyor. Yukarıda değinilmemekle birlikte bilinmeyen karakterle de uğraşmak zorunda kalabilirsiniz,
- Yine 2005 ve sonrası için eğer XML PATH yönteminde geçersiz karakter hatalarıyla uğraşmak istemiyorsanız CROSS APPLY tekniğini öneririm…
Merak edenler için
- DB2 için şu yazıyı = https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0058709.html
- Oracle için şunu = https://www.techonthenet.com/oracle/functions/listagg.php ve https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
- MySQL için ise şu sayfayı = https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
- Postresql ini de şu = https://coderwall.com/p/eyknwa/postgres-group_concat
inceleyebilirsiniz… Diğerlerini araştırmak da sizin ev ödeviniz olsun 🙂
SON NOT : SQL Server 2017 ile birlikte yeni fonksiyonlara sahip oluyoruz, bunlardan biri de bu makalenin konusunu karşılayan STRING_AGG fonksiyonu…
Yorum yapılmamış