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(‘‘ + REPLACE(keywords,’,’,’‘) + ‘‘ 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

给

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