SQL Server ASP.NET C# Kitabı

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 Ahmet Kaymaz
2 Mehmet Kaymaz

MusteriTelefon
————–
1 (0212)111
1 (0533)111
2 (0216)111
2 (0542)111

Ä°lk iÅŸlem olarak örneÄŸin ID’si 1 olan Ahmet Kaymaz’ı telefonlarını birleÅŸtirerek getirelim. Bunun için tipik string birleÅŸtirmesi yapacağız.

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.

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.

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.

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.

SELECT AdSoyad, dbo.GetTelefon(MusteriId) FROM Musteri

Ahmet Kaymaz (0212)111 ,(0533)111
Mehmet Kaymaz (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.

--FOR XML PATH, parametresi kullanıldığı zaman node adı olarak "row" yazar.
--Herhangi bir kolon adı belirtilmemişse
SELECT 'C#' FOR XML PATH
--C#

--Kolon adı belirtilmişse
SELECT 'C#' [YeniNesilDil] FOR XML PATH
--C#

/*Kolon adı belirtilirken ön ekler kullanılabilir.
@ işareti, o kolonun attribute olarak yazılacağını
@ işareti yerine olmayıp "/"slash içeriyorsa o kolon için bir XML hiyerarşi oluşturulur
* wildcard karakteri, kolon adını gözardı eder
*/
SELECT 'C#' [@YeniNesilDil] FOR XML PATH
--

SELECT MusteriId [@MId],AdSoyad FROM Musteri FOR XML PATH
--Ahmet Kaymaz

SELECT MusteriId [@MId],
AdSoyad "Musteri/Ad",Sehir "Musteri/Åžehir" FROM Musteri FOR XML PATH
/*

	
		Ali Korkmaz
		<Åžehir>Ä°stanbul
	

*/

SELECT MusteriId [@MId],
AdSoyad "*",Sehir "*" FROM Musteri FOR XML PATH
/*

	Ali Korkmaz Ä°stanbul


--FOR XML PATH'e parametre olarak geçilen ifade, "row" ifadesi yerine node adı olur.
SELECT AdSoyad FROM Musteri FOR XML PATH('Musteri')
--Ali Korkmaz
*/

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
--C#VB.NETSQL Server

Åž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.

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.

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 Ahmet Kaymaz (0212)111 ,(0533)111 ,
2 Mehmet Kaymaz (0216)111 ,(0542)111 ,
3 Ayşe Güler NULL