Sql Query for Join and convert Columns to csv

As mentioned in the solution 1, your database design is incorrect and I assume that it is an exisiting database and you are unable to change the database structure

This is the query

with testdata (ProductID, ItemID, Keywords) as (

select tbl_Products.ID,b.items,tbl_Keywords.Keywords
from tbl_Products
cross apply dbo.Split(tbl_Products.KeywordIDs ,',') b
left join tbl_Keywords on b.items=tbl_Keywords.ID
) select distinct ProductID ,substring( (
            select  ','+ Keywords
            from testdata inner1
            where inner1.ProductID =outer1.ProductID  for XML path('')
                  ),2,500)keywordCSV
from testdata outer1

and you needs to have the following function created in your database prior to the query execution

Create FUNCTION [dbo].[Split] (@String varchar(8000), @Delimiter char(1))
   returns @temptable TABLE (items varchar(8000))
   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

GO

I have tested the solution in SQL server 2008 and it works, if you have any issues let me know the error message along with your SQL server version.

I got a solution

declare @tbl_Products table(ID int, Keywords varchar(10))

insert into @tbl_Products values
(1,   '1,2'),
(2,   '1,3'),
(3,   '1,4'),
(4,   '2,4')

declare @tbl_Keywords table(Key_ID int, Keyword varchar(10))

insert into @tbl_Keywords values
(1,        'Keyword1'),
(2,        'Keyword2'),
(3,        'Keyword3'),
(4,        'Keyword4')

select P.ID,
       P.Keywords,
       stuff((select ', '+K.keyword
              from @tbl_Keywords as K
                inner join P.XKeywords.nodes('/k') as KX(N)            
                  on KX.N.value('.', 'int') = K.Key_ID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as keywordCSV
from (
      select ID,
             Keywords,
             cast('<k>'+replace(Keywords, ',', '</k><k>')+'</k>' as xml) as XKeywords
      from @tbl_Products
     ) as P​

Thanks All for Reply.