SQL查询计数频率

本文介绍了SQL查询计数频率的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张桌子，上面有

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

我想做一张桌子

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

以此类推.

推荐答案

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

给予

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