Konuyu Paylaş : facebook gplus twitter

Konuyu Oyla:
  • Derecelendirme: 5/5 - 3 oy
  • 1
  • 2
  • 3
  • 4
  • 5
SQL Server : GROUP_CONCAT ve LISTAGG Simülasyonu
#1
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

Kod:
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;

Kod:
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)

Kod:
CROSS APPLY        96,77 ms
XML Path           98,47 ms
QUIRKLY Update    267,72 ms
RECURSIVE CTE     486,33 ms
CURSOR           2198,51 ms

SONUÇ

* 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ı        = Linkleri Görebilmeniz İçin Giriş yap veya Üye Ol
* Oracle için şunu          = Linkleri Görebilmeniz İçin Giriş yap veya Üye Ol ve Linkleri Görebilmeniz İçin Giriş yap veya Üye Ol
* MySQL için ise şu sayfayı = Linkleri Görebilmeniz İçin Giriş yap veya Üye Ol
* Postresql ini de şu       = Linkleri Görebilmeniz İçin Giriş yap veya Üye Ol

inceleyebilirsiniz... Diğerlerini araştırmak da sizin ev ödeviniz olsun arkadaşım Smile

SON NOT : SQL Server 2017 ile birlikte yeni fonksiyonlara sahip oluyoruz, bunlardan biri de bu makalenin konusunu karşılayan STRING_AGG fonksiyonu...
Cevapla

Konuyu Paylaş : facebook gplus twitter



Konu ile Alakalı Benzer Konular
Konular Yazar Yorumlar Okunma Son Yorum
  SQL Server : Tablo Değişkeni Kullanımı (Running Total, Yürüyen Bakiye Örneği) uparlayan 5 258 22-03-2018, Saat: 12:28
Son Yorum: hi_selamlar
  MS-SQL Server'da tekrar eden (çift, mükerrer) kayıtları tespit etme ve silme. csunguray 6 247 29-01-2018, Saat: 18:29
Son Yorum: adelphiforumz
  Datasnap + Rest Server -> Evrensel Veri Adaptörü - 001 - Sunucu Uygulaması mad85 7 494 17-11-2017, Saat: 22:56
Son Yorum: mad85
  SQL Server : Trigger hangi kipte çalışıyor uparlayan 9 869 13-09-2017, Saat: 14:43
Son Yorum: uparlayan
  SQL Server : Tablolar için parametrik sıralı alan listesi uparlayan 3 439 01-07-2017, Saat: 19:12
Son Yorum: uparlayan



Konuyu Okuyanlar: 1 Ziyaretçi