15 Mayıs 2017 Pazartesi

Kolondaki Verileri Tek Satırda Göstermek (Concatenate İşlemi)

Kolondaki Verileri Tek Satırda Göstermek (Concatenate İşlemi)

T-SQL’de iki string’i birleştirmek için CONCAT metodu kullanılır. Bazı durumlarda aynı satırdaki verileri değil alt altta durunda verileri birleştirmek isteyebiliriz. Bunun için klasik değer birleştirilme yöntemi kullanılır. Örneğin bir müşteriye ait ikinci bir tabloda row olarak duran birden fazla telefonunu, okuduğu gazeteleri, hobilerini okuyup tek bir satırda gösterme ihtiyacı duyabiliriz. Musteri ve MusteriTelefon tabloları olduğunu düşünelim.
Musteri
—–
1 Sencer BELGE
2 Mehmet BELGE

MusteriTelefon
—–
1 (0212)111
1 (0533)111
2 (0216)111
2 (0542)111
İlk işlem olarak örneğin ID’si 1 olan Sencer BELGE’ı telefonlarını birleştirerek getirelim. Bunun için tipik string birleştirmesi yapacağız.
1
2
3
4
5
6
7
DECLARE @Telefonlar varchar(50)
SET @Telefonlar=''--Sonuç NULL olmasın diye
 
SELECT @Telefonlar = @Telefonlar + ','+ MT.Telefon
FROM MusteriTelefon MT WHERE MT.MusteriId=1
 
SELECT @Telefonlar
,(0212)111 ,(0533)111 Bu ifade de @Telefonlar değişkenin ilk değeri NULL olduğu öncelikle boş değer vermek zorundayız. Çünkü T-SQL’de ‘A’+NULL ifadesi gibi NULL ile işleme giren ifadenin sonucu NULL olur. Aynı şekilde MusteriTelefon tablosunda NULL kayıt olsaydı sonuç yine NULL olacaktı. Bunları çözmek için ISNULL() metodu kullanılır. İki parametre alan bu metod, ilk değer NULL olduğu zaman ikinci parametredeki değeri döndürür.
1
2
3
4
5
6
7
DECLARE @Telefonlar varchar(50)
SET @Telefonlar=''
 
SELECT @Telefonlar = @Telefonlar + ISNULL(','+ MT.Telefon,'')
FROM MusteriTelefon MT WHERE MT.MusteriId=1
 
SELECT @Telefonlar
Bu şekilde @Telefonlar değişkeninin her defasında NULL olup olmamasıyla uğraşmamak için T-SQL’in COALESCE() metodu önerilir. Bu metod, parametre olarak değerler listesini alarak bu değerler içerisinde NULL olmayan ilk kaydı döndürür. SELECT COALESCE(NULL,NULL,’A’,NULL,’B’)ifadesinin sonucu “A” olarak döner. Örnek queryi aşağıdaki gibi düzenleyelim.
1
2
3
4
5
6
DECLARE @Telefonlar varchar(50)
 
SELECT @Telefonlar = COALESCE(@Telefonlar + ',', '') + ISNULL(MT.Telefon,'')
FROM MusteriTelefon MT WHERE MT.MusteriId=1
 
SELECT @Telefonlar
Her müşterinin telefonlarını bu şekilde listelemek için telefonları birleştiren bir fonksiyon hazırlamalıyız. Musteri tablosunu listelerken MusteriId’sini parametre alacak bu fonksiyonu da SELECT içerisinde çağırmalıyız.
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE FUNCTION dbo.GetTelefon
(
    @MusteriId int
)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @Telefonlar varchar(50)
    SELECT @Telefonlar = COALESCE(@Telefonlar + ',', '') + ISNULL(MT.Telefon,'')
    FROM MusteriTelefon MT WHERE MT.MusteriId=@MusteriId
 
    RETURN @Telefonlar
END
Her müşteri için bu fonksiyonu çağıralım.
1
SELECT AdSoyad, dbo.GetTelefon(MusteriId) FROM Musteri
Sencer BELGE (0212)111 ,(0533)111
Mehmet BELGE (0216)111 ,(0542)111
Bu yazıda asıl bahsetmek istediğim nokta, konuyla ilgili SQL Server 2005 ile birlikte gelmiş özelliklerdir. Bunların ilki FOR XML PATH ifadesidir. Bu ifade, sorgulanan kolondaki kayıtları XML formatında listelerken nasıl bir prefix ve root yapısı kullanacağımızı, kolonu XML node olarak mı yoksa attribute olarak mı gösterebileceğimizi kolayca belirtmemize ve geri dönen kayıtları normal bir kolon gibi XPATH mantığıyla sorgulamamıza imkan tanır. Aşağıdaki örnek satırları inceleyelim.
1
2
3
4
5
6
7
8
9
10
--FOR XML PATH, parametresi kullanıldığı zaman node adı olarak "row" yazar.
--Herhangi bir kolon adı belirtilmemişse
SELECT 'C#' FOR XML PATH
--
Eğer kolon adı olarak XML PATH'in özel ifadelerinden "data()" girilirse herhangi bir node adı oluşturulmaz sadece kolondaki veri gösterilir.
SELECT KursAd "data()" FROM Kurs FOR XML PATH
--
Şimdi örneğimize geri dönelim. Telefon kayıtlarını yan yana yazarken kolon adı olarak boşluğu girmesi için FOR XML PATH(") ifadesini ayrıca "row" veya kolon adını node ismi kullanmaması sadece kolon verisini göstermesi için kolon adı olarak "data()" ifadesini kullancağız.
SELECT Telefon [data()] FROM MusteriTelefon
FOR XML PATH('')
(0212)111 (0533)111 (0216)111 (0542)111 Çıkan bu sonuçtaki boşluk ifadeleri virgülle replace edelim.
1
2
3
4
5
6
7
SELECT MusteriId,
REPLACE(
(
    SELECT RTRIM(Telefon) [data()] FROM MusteriTelefon
    FOR XML PATH('')
),' ',',')
FROM MusteriTelefon
1 (0212)111,(0533)111,(0216)111,(0542)111
1 (0212)111,(0533)111,(0216)111,(0542)111
1 (0212)111,(0533)111,(0216)111,(0542)111
2 (0212)111,(0533)111,(0216)111,(0542)111
2 (0212)111,(0533)111,(0216)111,(0542)111 örnekte Telefonların sonunda boşluk bırakmaması için [data()] yerine [text()] kullanılabilir.
Böylece her müşterinin birden fazla telefonunu tek satırda getirmiş olduk.
Bu işlemi yapabilmek için kullanacağımız diğer yöntem yine SQL 2005’in süper özellik olan “CROSS APPLY” operatörüdür. İki yeni operatör olan “CROSS APPLY” ve “OUTER APPLY”operatörler, tablo türünde sonuç döndüren fonksiyon veya subquery’lerle normal tablo veya viewleri JOIN etmemizi sağlar. En önemli özellikleri JOIN esnasında fonksiyonlara ilgili tablo ve view’a ait bir kolonu dinamik olarak parametre geçebiliyor olmamızdır. CROSS APPLY her iki tarafta eşleşen kayıtları getirirken, OUTER APPLY, eşleşen kayıt olmadığı zaman satırın kendisini ve karşılığında NULL değer getirir.
1
2
3
4
5
6
7
SELECT M.MusteriId,M.AdSoyad,
T.Telefonlar
FROM Musteri M CROSS APPLY(
    SELECT Telefon+',' AS [text()] FROM MusteriTelefon MT
    WHERE MT.MusteriId=M.MusteriId
    FOR XML PATH('')
) T(Telefonlar)--Kolon Adı
1 Sencer BELGE (0212)111 ,(0533)111 ,
2 Mehmet BELGE (0216)111 ,(0542)111 ,
3 Ayşe Güler NULL
Gönderen zaman: Hiç yorum yok:
Kaydol: Kayıtlar (Atom)