sql server - Split and concatenate values in SQL -

I have two separate tables below and there is a specific requirement for SQL queries.

  Table 1: Name rule number Tom 1,2 Pete 1,3 Table 2: Rule number Description 1 Rule 1 2 Rule 2 3 Rule 3   
 How can I get a SQL query result?  
  Description of the name Tom Rule1, Rule2 Pete Rule 1, Rule 3    
 
 
 You need the first custom partitioning function to separate the delimited list, and then to combine the details  for the XML path . Here is your last query  
  select t1.Name, STUFF ((SELECT ',' +2 as the description, select T2 WHERE t2.ruleNumber in (dbo.fn_split) (t1.RuleNumber , ',')) For the XML path ('')), 1, 1, '' by the rule number, from table 1 t1 to 'description' as part of the   
 partition function Code for [Fn_Split] (@ string varchar (8000), @dailymeter varchar (10)) begins as @ttable table (s type (1000)) @ i int, @j int select @i = 1 while @i & Lt; Select = len (@ string) Select @J = Charindex (@ Delimiter, @ string, @i) if start by selecting @J = 0 @J = Lena (@string) + 1 end Enter @tbl Select substring (@ string , @E, @JE - @i) Select @E = @Jey + Lane (Delimiter) End End End