SQL query to count frequency

Question

I've got a table which has entries like

id     keywords
1      cat, dog, man, mouse
2      man, pen, pencil, eraser
3      dog, man, friends
4      dog, leash,......

I want to make a table something like

id  cat  dog  man  mouse  pen  pencil  eraser  friends  leash ......
1   1    1    1    1      0    0       0       0        0
2   0    0    1    0      1    1       1       0        0
3   0    1    1    0      0    0       0       1        0

and so on.

Answer 1

WITH basedata(id,keywords) AS
(
SELECT 1,'cat, dog, man, mouse' union all
SELECT 2 ,'man, pen, pencil, eraser' union all
SELECT 3,'dog, man, friends' union all
SELECT 4,'dog, leash'
),
cte(id, t, x)
     AS (SELECT *,
                CAST('<foo>' + REPLACE(keywords,',','</foo><foo>') + '</foo>' AS XML)
         FROM   basedata)   
SELECT id,
       LTRIM(RTRIM(w.value('.', 'nvarchar(max)'))) as keyword
INTO #Split       
FROM   cte
       CROSS APPLY x.nodes('//foo') as word(w) 


DECLARE @ColList nvarchar(max)

SELECT @ColList = ISNULL(@ColList + ',','') + keyword 
FROM (
SELECT DISTINCT QUOTENAME(keyword) AS keyword
FROM #Split
) T

EXEC(N'       
SELECT *
FROM #Split
PIVOT (COUNT(keyword) FOR keyword IN (' + @ColList + N')) P')

DROP TABLE #Split

Gives

id          cat         dog         eraser      friends     leash       man         mouse       pen         pencil
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           1           1           0           0           0           1           1           0           0
2           0           0           1           0           0           1           0           1           1
3           0           1           0           1           0           1           0           0           0
4           0           1           0           0           1           0           0           0           0

Answer 2

Must you use the pivot form? And is your end result the frequency per id - which seems strange? Otherwise the cells always contain 1 as frequency.

See if this works for you.

Sample data

create table basedata(id int,keywords varchar(max));
insert basedata
SELECT 1,'cat, dog, man, mouse' union all
SELECT 2 ,'man, pen, pencil, eraser' union all
SELECT 3,'dog, man, friends' union all
SELECT 4,'dog, leash'

Query

;with cte(id, list, word) as (
select id,
    cast(STUFF(keywords,1,CHARINDEX(',',keywords+','),'') as varchar(max)),
    cast(ltrim(rtrim(LEFT(keywords,CHARINDEX(',',keywords+',')-1))) as varchar(max))
from basedata
where keywords > ''
union all
select id,
    STUFF(list,1,CHARINDEX(',',list+','),''),
    ltrim(rtrim(LEFT(list,CHARINDEX(',',list+',')-1)))
from cte
where list > ''
)
select word, COUNT(*) frequency
from cte
group by word

Output

word       frequency
---------- -----------
cat        1
dog        3
eraser     1
friends    1
leash      1
man        3
mouse      1
pen        1
pencil     1