I want to avoid dynamic pivot in SQL Server.

How to avoid cursor in sql server? also want to avoid while loop

• I have two tables in my database, 1st one contains a couple of sentences, like 'I like apples and bananas', 2nd one contains key word, like 'apple' & 'orange'. I want to create a sql statements or usp to list all the colomns in the 1st table with the keywords in the 2nd one. How can I achieve that without using cursors? Would anyone have some smart ideas rather than while loop? That'll be great. Thanks, Ling

• Answer:

  See if that's what you need... Option 1 would return a row for each keyword matching the phrase... Option 2 returns a CSV of the keywords that are in the phrase. Note that this uses SQL 2005 related functions. I've made a test case for you... Perhaps would help your explanation of the problem. create table #test(id int identity(1,1), phrase varchar(1000)) create table #kenter code hereeyword(id int identity(1,1), keyword varchar(50)) insert into #test select 'I like apples and bananas' insert into #keyword select 'APPLE' UNION select 'BANANA' UNION select 'RASPBERY' select t.*, k.keyword from #test t inner join #keyword k on t.phrase like '%' + k.keyword + '%' --OR... select t.*, Keywords = ( select k.keyword + ',' as [text()] from #keyword k where t.phrase like '%' + k.keyword + '%' FOR XML PATH('')) from #test t drop table #test drop table #keyword