Função de divisão no SQL Server 2008

Eu tenho Table1 com colunas como esta:

 +--+------+ |ID|Name | +--+------+ |1 |MSSQL | +--+------+ |2 |MySQl | +--+------+ |3 |Oracle| +--+------+ 

Na Table2 , tenho uma coluna como

 +------------+ |Databasename| +------------+ |1,3 | +------------+ |2 | +------------+ |1,2 | +------------+ 

Minha saída deve ser:

 +------------+ |Databasename| +------------+ |MSSQL,Oracle| +------------+ |MySQL | +------------+ |MSSQL,MYSQL | +------------+ 

Como obtenho isso, preciso de consulta para isso ..

Você está solicitando uma function de divisão, mas não precisa dividir seus valores para obter o resultado desejado.

Essa consulta cria a lista de nomes separados por vírgula em uma subconsulta correlacionada usando o truque for xml para concatenar valores. Ele usa like para descobrir quais valores usar da Table1 para cada linha na Table2 .

 select ( select ', '+T1.Name from Table1 as T1 where ','+T2.Databasename+',' like '%,'+cast(T1.ID as varchar(10))+',%' for xml path(''), type ).value('substring(text()[1], 3)', 'varchar(max)') as Databasenames from Table2 as T2 

Violino SQL

Primeiro, sua melhor solução é não armazenar dados em uma lista separada por vírgulas em seu database. Você deve considerar corrigir a estrutura da tabela.

Se você não puder alterar a estrutura da tabela, precisará dividir os dados na lista em linhas para atribuir o nome correto. Depois que os dados são divididos, você pode concatenar os dados de volta à lista.

Há muitas funções diferentes que você pode encontrar on-line, mas aqui está uma versão que eu geralmente uso:

 CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1)) returns @temptable TABLE (items varchar(MAX)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end; 

Para obter seu resultado, eu começaria aplicando a function split e um row_number() pois não vejo uma chave exclusiva associada a cada linha. Se você tiver uma chave única em cada linha, você não precisará do row_number() :

 ;with cte as ( select rn, name, id from ( select row_number() over(order by (select 1)) rn, databasename from table2 ) t2 cross apply dbo.split(t2.databasename, ',') i inner join table1 t1 on i.items = t1.id ) select * from cte 

Essa consulta divide sua lista separada por vírgula nos seguintes itens:

 | RN | NAME | ID | -------------------- | 1 | MSSQL | 1 | | 1 | Oracle | 3 | | 2 | MySQl | 2 | | 3 | MSSQL | 1 | | 3 | MySQl | 2 | 

Depois de ter os dados em várias linhas com o name correto, você poderá usar STUFF() e FOR XML PATH para concatená-los na lista. Você consulta completa seria semelhante a isto:

 ;with cte as ( select rn, name, id from ( select row_number() over(order by (select 1)) rn, databasename from table2 ) t2 cross apply dbo.split(t2.databasename, ',') i inner join table1 t1 on i.items = t1.id ) select STUFF( (SELECT ', ' + c2.name FROM cte c2 where c1.rn = c2.rn order by c2.id FOR XML PATH ('')) , 1, 1, '') Databasename from cte c1 group by c1.rn order by c1.rn; 

Veja SQL Fiddle with Demo .

O resultado da consulta completa é:

 | DATABASENAME | ------------------ | MSSQL, Oracle | | MySQl | | MSSQL, MySQl | 

Sem divisão, também sem XML Path, mas consegue o resultado certo.

 ;with cte as ( select *, cast(null as varchar(1024)) as str, cast(0 as int) as ID from Table2 union all select DatabaseName, (case when DatabaseName like cast(t.ID as varchar(32)) + ',%' or DatabaseName like '%,' + cast(t.ID as varchar(32)) + ',%' or DatabaseName like '%,' + cast(t.ID as varchar(32)) or DatabaseName = cast(t.ID as varchar(32)) then cast(isnull(str, '') + ',' + t.Name as varchar(1024)) else str end), cte.ID + 1 as ID from cte inner join Table1 t on cte.ID + 1 = t.ID ) select DatabaseName, (case when str like ',%' then substring(str, 2, len(str)) else null end) as str from cte c where ID = (select max(ID) from cte where DatabaseName = c.DatabaseName) 
 --Here it goes: ---------------- -- FieldCount -- ---------------- CREATE FUNCTION [dbo].[FieldCount](@S VARCHAR(8000), @Separator VARCHAR(10)) RETURNS INT AS BEGIN /* @Author: Leonardo Augusto Rezende Santos @Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890 */ DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Result INT IF @Separator = ' ' BEGIN SET @S = REPLACE(@S, ' ', '|-|') SET @Separator = '|-|' END WHILE CHARINDEX(@Separator + @Separator, @S) > 0 SET @S = Replace(@S, @Separator + @Separator, @Separator + '_-_' + @Separator) IF @S <> '' SET @Result = 1 ELSE BEGIN SET @Result = 0 RETURN(@Result) END SET @Ptr = 0 SET @LenS = LEN(@S) SET @LenSep = LEN(@Separator) SET @p = CHARINDEX(@Separator, @S) WHILE @p > 0 BEGIN SET @Result = @Result + 1 SET @Ptr = @Ptr + @p + @LenSep SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1)) END RETURN(@Result) END -------------- -- GetField -- -------------- CREATE FUNCTION [dbo].[GetField](@S VARCHAR(8000), @Separator VARCHAR(10), @Field INT) RETURNS VARCHAR(8000) AS BEGIN /* @Author: Leonardo Augusto Rezende Santos @Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890 */ DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Fld INT, @Result VARCHAR(8000) IF @Separator = ' ' BEGIN SET @S = REPLACE(@S, ' ', '|-|') SET @Separator = '|-|' END IF @Field > dbo.FieldCount(@S, @Separator) BEGIN SET @Result = '' RETURN(@Result) END SET @Fld = 1 SET @Ptr = 1 SET @LenS = LEN(@S) SET @LenSep = LEN(@Separator) SET @p = CHARINDEX(@Separator, @S) WHILE (@p > 0) and (@Fld < @Field) BEGIN SET @Fld = @Fld + 1 SET @Ptr = @Ptr + @p + @LenSep - 1 SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1)) END IF (@p = 0) and (@Fld = @Field) SET @p = @LenS - @Ptr + 2 SET @Result = SUBSTRING(@S, @Ptr, @p - 1) RETURN(@Result) END /* USAGE*/ select dbo.FieldCount('A1 A2 A3 A4 A5', ' ') --It will return 5 select dbo.GetField('A1 A2 A3 A4 A5', ' ', 3) --It will return 'A3' select dbo.GetField('A1/A2/A3/A4/A5', '/', 3) --It will return 'A3' --Hope it works for you. --Leonardo Augusto