I have the following input.
INPUT:
TableA
ID Sentences
--- ----------
1 I am a student
2 Have a nice time guys!
What I need to do is to extract the words from the sentence(s)
and insert each individual word in another table
OUTPUT:
SentenceID WordOccurance Word
---------- ------------ -----
1 1 I
1 2 am
1 3 a
1 4 student
2 1 Have
2 2 a
2 3 nice
2 4 time
2 5 guys!
I am using SQL Server 2005.
My fruitless approach so far is
;With numCTE As
(
Select rn = 1
Union all
Select rn+1 from numCTE where rn<1000)
,
getWords As
(
Select rn, ID, indiChars
From numCTE
Cross Apply(Select ID, indiChars = Substring(Sentences,1,rn) From inputTbl)x
where indiChars <> ''
)
Select Id, Word = stuff(select ',' + cast(indiChars)
from getWords g1
where g1.Id = g2.Id
for xml path(''),'',1,1)x
from getWords g2
Group by g2.Id
I am looking for a set based solution.
Thanks
