Virgül ile ayrılmış iki tabloyu Join-lemek

Bu gün uğraşmak zorunda kaldığım bir sorgu ile ilgili kodları paylaşmak istiyorum.
virgülle ayrılmış iki nvarchar kolonun birbiri ile nasıl join yapılabileceğinden söz etmek istiyorum.
Bu tür yapılar kesinlikle önerilmez, join yapılacaksa bu tagler için ayrı tablolar oluşturulmalı ve kayıtlar dikey olarak saklanmalıdır. Ancak bir yazılım projesinin köşeleri her zaman belirli ve sabit olmuyor. Bu tür istekler her zaman olabilir.

Benim yaşadığım sorunda iki tablo vardı. her ikisinde de virgüllerle ayrılmış kayıtlar vardı.  Aşağıda tabloların örneklerini bulabilirsiniz.

yapılmak istenen Tablo1 deki virgülle ayrılmış her bir kelimeyi Tablo2 de yine virgülle ayrılmış her bir satır içinde aramak.
Resim ile anlatmaya çalışırsam…

özet olarak, Tablo1 de ki her tag tablo2 de ki her tag ile eşleşiyor mu diye sorgulamamız gerekiyor.
Böyle bir durumda best practices nedir diye düşündüm, stackoverflow da da sordum ama pek bir şey çıkmadı doğrusu.
Bu yüzden belki birilerine lazım olur / daha iyi bir çözüm tavsiyesinde bulunan olur diye buradan paylaşıyorum.

Not: full-text yapısı kullanılmıyor

bu kadar girizgahtan sonra çözüme bakalım… Evvela örnek tablolarımızı oluşturalım.

CREATE TABLE [dbo].[Table1](
Id      int not null,
Name    nvarchar(250) not null,
Tag     nvarchar(250)  null,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2](
Id      int not null,
Name    nvarchar(250) not null,
Tag     nvarchar(250)  null,
) ON [PRIMARY]
GO

Şimdi Table1 i örnek verilerle dolduralım, bunun için AdventureWorks databasesi AdventureWorks.SalesLT.Customer  tablosundan yararlandım.

INSERT INTO Table1
SELECT CustomerID,CompanyName, (FirstName + '','' + LastName)
FROM AdventureWorks.SalesLT.Customer 
GO 3 

toplamda 28000 Table1 e eklenmiş oldu, Şimdi Table2 de benzer Tagler olması için Table1 de rastgele tabler alıp bunları Table2 ye ekliyorum ki Table1 ve Table2 taglerinde eşleşen kayıtlar olsun.

declare @tag1 nvarchar(50) = ''Donna,Carreras''
declare @tag2 nvarchar(50) = ''Johnny,Caprio''

Şimdide Table2 için örnek kayıtlar oluşturalım. Bunun içinde AdventureWorks.SalesLT.Product  tablosundan yararlanıyorum. Birde logic koydum eklenen kaydın ProductId nin sağdan ilk rakamı 5 ten büyükse Tag kolonuna @tag1 i yazsın değilse @tag2 yi yazsın.

INSERT INTO Table2
  SELECT ProductID,Name, (case when(right(ProductID,1)>=5) then  @tag1 else @tag2 end)
  FROM AdventureWorks.SalesLT.Product 
 GO 3 

ve virgül ile ayrılmış bir metni gönderdiğimizde onu parçalayıp bize bir table dönecek olan function umuzu da yazıyoruz

CREATE FUNCTION [dbo].[StringToTable]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH StringToTable(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM StringToTable
            WHERE endpos > 0
    )
    SELECT ''Id'' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        ''Data'' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM StringToTable
)

Test ortamımızı oluşturduktan sonra artık sorgumuzu oluşturmaya başlayabiliriz.

öncelikle Table1 de ki bütün tagleri bir temp tabloda dikey olarak yani her tag bir satıra denk gelecek şekilde eklemek üzere #Tags tablomuzu oluşturuyoruz.

create TABLE #Tags (  Tag nvarchar(250) NOT NULL );

Table1 içindeki tag satırları için bir döngü oluşturacağız ve her satırdaki tag kolonunu yukarda oluşturduğumuz metni tabloya dönüştüren sql functiona gönderip, bu functiondan dönen satırları

#Tags tablosuna ekleyeceğiz. İşin bu kısmında WHILE kullanacağım siz isterseniz CURSOR ile de yapabilirsiniz ama While cursore göre daha performanslı çalıştığı için onu seçmenizde fayda var.

declare @Rows int = 1
declare @Tag nvarchar(1024)
declare @Id int = 0

WHILE @Rows>0
BEGIN
    Select Top 1 @Tag=Tag,@Id=Id from Table1 where Id>@Id
    set @Rows =@@RowCount
    if @Rows>0
    begin
        insert into #Tags(Tag)   SELECT Data FROM dbo.StringToTable(@Tag, '','')
    end
END

Elimizde Table1 de kullanılan bütün taglerden 1 tane dikey bir tabloda mevcut. Şimdi bu temp tablo ile Table2 yi join yapıp işlemi tamamlamaya çalışalım.

select distinct t.*
from Table2 t  
    inner join  #Tags on ('','' + t.Tag + '','') like (''%,'' + #Tags.Tag + '',%'')

Sonuç : Table1 kayıt sayısı : 28000 Table2 satır sayısı=9735  çalıştığım sorgu 2 saniyeden daha az sürdü.

Kabul edilebilir bir süre değil ama günü kurtarmak için yeterli :d

 

2015 : memet tayanç