sql server - Split column with delimited values for an inner join -

sql server - Split column with delimited values for an inner join -

i need perform inner bring together column containing delimited values like:

123;124;125;12;3433;35343;

now doing this:

alter procedure [dbo].[getfruitdetails] ( @crateid int ) select fruits.*, fruits_crates.crateid fruits_crates inner bring together fruits on fruits_crates.fruitid = fruits.id fruits_crates.crateid = @crateid

now issue saving info way:

fruitcrateid fruitid 1 1; 2 1;2;3;4 3 3;

how can inner bring together fruitsids fruit table fruit details well?

using method posted in this answer, can convert delimited string rows of temp table , bring together that:

sql fiddle

schema setup:

create table fruits_crates ([fruitcrateid] int, [fruitid] varchar(10)) ; insert fruits_crates ([fruitcrateid], [fruitid]) values (1, '1;'), (2, '1;2;3;4;'), (3, '3;') ; create table fruits ([fruitid] int, [fruitname] varchar(10)) ; insert fruits ([fruitid], [fruitname]) values (1, 'apple'), (2, 'banana'), (3, 'orange'), (4, 'pear') ;

insert temp table:

select a.[fruitcrateid], split.a.value('.', 'varchar(100)') fruitid #fruits (select [fruitcrateid], cast ('<m>' + replace([fruitid], ';', '</m><m>') + '</m>' xml) string fruits_crates) cross apply string.nodes ('/m') split(a)

join temp table lookup:

select t1.*, t2.fruitname #fruits t1 inner bring together fruits t2 on t1.fruitid = t2.fruitid

results:

| fruitcrateid | fruitid | fruitname | |--------------|---------|-----------| | 1 | 1 | apple | | 2 | 1 | apple | | 2 | 2 | banana | | 2 | 3 | orange | | 2 | 4 | pear | | 3 | 3 | orange |

sql-server sql-server-2008 tsql