SQL Mükkerrer Satırları Yan Yana Getirme

MS SQL de bazı durumlarda özellikle mükkerrer olan kayıtları aynı satırda yan yana getirme ihtiyacı olabilir. Genellikle veri günceleme yaparken güncellenecek satıra birden çok satır mükerrer veri geliyorsa güncelleme işlemi sonucu sadece bir satır bilgi güncellenir ve diğer veriler kayıp olur. Böyle durumlarda bütün verilerin yan yana getirilmesi veya biriktirilmesi gerekebilir. Bu soruna çözüm için MS SQL de en iyi yöntem Stuff ifadesi kullanarak mükerrer kayıtları tek satırda yan yana göstermek olacaktır. Ayrıca raporlama amaçlı olarak da bir grubu ait detayları virgül ile ayırarak yan yana getirilebilir.

Öncelikle örnek bir veri seti için geçici bir tablo oluşturalım.

SELECT 1 AS ID, 'ADANA' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE
INTO #TABLO
UNION
SELECT 1 AS ID, 'ANTALYA' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE UNION
SELECT 1 AS ID, 'BURDUR' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE UNION
SELECT 1 AS ID, 'HATAY' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE UNION
SELECT 1 AS ID, 'ISPARTA' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE UNION
SELECT 1 AS ID, 'KAHRAMANMARAŞ' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE UNION
SELECT 1 AS ID, 'MERSİN' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE UNION
SELECT 1 AS ID, 'OSMANİYE' AS SEHIR,'AKDENİZ BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'AĞRI' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'ARDAHAN' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'BİNGÖL' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'BİTLİS' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'ELAZIĞ' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'ERZİNCAN' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'ERZURUM' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'HAKKARİ' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'IĞDIR' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'KARS' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'MALATYA' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'MUŞ' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'TUNCELİ' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 2 AS ID, 'VAN' AS SEHIR,'DOĞU ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'AFYON' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'AYDIN' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'DENİZLİ' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'İZMİR' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'KÜTAHYA' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'MANİSA' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'MUĞLA' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 3 AS ID, 'UŞAK' AS SEHIR,'EGE BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'ADIYAMAN' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'BATMAN' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'DİYARBAKIR' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'GAZİANTEP' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'KİLİS' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'MARDİN' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'SİİRT' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'ŞANLIURFA' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 4 AS ID, 'ŞIRNAK' AS SEHIR,'GÜNEYDOĞU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'AKSARAY' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'ANKARA' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'ÇANKIRI' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'ESKİŞEHİR' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'KARAMAN' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'KAYSERİ' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'KIRIKKALE' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'KIRŞEHİR' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'KONYA' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'NEVŞEHİR' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'NİĞDE' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'SİVAS' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 5 AS ID, 'YOZGAT' AS SEHIR,'İÇ ANADOLU BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'AMASYA' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'ARTVİN' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'BARTIN' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'BAYBURT' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'BOLU' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'ÇORUM' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'DÜZCE' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'GİRESUN' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'GÜMÜŞHANE' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'KARABÜK' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'KASTAMONU' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'ORDU' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'RİZE' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'SAMSUN' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'SİNOP' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'TOKAT' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'TRABZON' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 6 AS ID, 'ZONGULDAK' AS SEHIR,'KARADENİZ BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'BALIKESİR' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'BİLECİK' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'BURSA' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'ÇANAKKALE' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'EDİRNE' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'İSTANBUL' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'KIRKLARELİ' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'KOCAELİ' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'SAKARYA' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'TEKİRDAĞ' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE UNION
SELECT 7 AS ID, 'YALOVA' AS SEHIR,'MARMARA BÖLGESİ' AS BOLGE

Oluşturulan tablo aşağıdaki gibidir.
SELECT * FROM #TABLO 

Şehir alanında yana yana getiren T-SQL sorgusu:

SELECT A2.ID,A2.BOLGE,
STUFF( (SELECT '; ' + RTRIM(A1.SEHIR)
FROM #TABLO AS A1
WHERE A1.ID= A2.ID ORDER BY SEHIR FOR XML PATH('')), 1, 1, '' ) AS SEHIRLER
FROM #TABLO AS A2 GROUP BY A2.ID,A2.BOLGE
ORDER BY 1,2