Cross apply and table-valued function to correlate query __ function

The present scene is to have an interest hobby type table and an employee Hobby table, a list of hobbies in the employee's Hobby list, if the employee has more than one hobby, then put these hobbies into a record, hobbies separated by commas, now needs to be each employee of these hobbies separated into a record query out, This can be done for example, to do employee hobby statistics applications.

Hobby Type Table T_a

Employee Hobby Table T_b

The result of the need is

Create a table-valued function that separates strings

Create function [dbo]. [Splitids] (
    @Ids nvarchar (1000),
    @split_char nvarchar)
returns @t_id TABLE (id bigint) as
begin
    declare @i int,@j int,@l int,@v bigint;
    Set @i = 0;
    Set @j = 0;
    Set @l = Len (@Ids);
    while (@j < @l)
    begin
       Set @j = charindex (@split_char, @Ids, @i+1);
       if (@j = 0) Set @j = @l+1;
       Set @v = Cast (SUBSTRING (@Ids, @i+1,@j-@i-1) as bigint);
       INSERT into @t_id VALUES (@v)
       set @i = @j;
    End return
    ;
End Go

Use the following statement to get the desired result

Select 
    AID = T_a.id
    , aname = T_a.name
    , bid = T_b.id,t_b.name
from T_b cross
apply Splitids (A_ids, ', ') tbl_ids
INNER JOIN t_a on tbl_ids. id = t_a.id

Another way of writing a split string function

Use [master]
go
/****** Object:  userdefinedfunction [dbo].[ Split]    Script date:05/08/2014 14:45:25 ******/
SET ansi_nulls on
go
SET quoted_identifier
on Go
ALTER   FUNCTION [dbo].[ Split]   
(   
@c VARCHAR (MAX),   
@split VARCHAR)   
RETURNS @t TABLE (col VARCHAR)   
as< C22/>begin  
    while (CHARINDEX (@split, @c) <> 0)   
        BEGIN  
            INSERT  @t (col)   
            VALUES  ( SUBSTRING (@c, 1, CHARINDEX (@split, @c)-1))   
            SET @c = STUFF (@c, 1, CHARINDEX (@split, @c), "")   
        end  
    insert
  @t (COL) VALUES  (@c) return end

-------------------------------end of FUNCTION----------------------------------------------

How to use

SELECT * from Split (' 1,2,3 ', ', ')