SQL查询计数频率

徐君源

我有一张表，上面有像
id     keywords
1      cat, dog, man, mouse
* D7 e/ u, D- g2      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
) |* ^1 U0 W9 l* X1 Y9 m$ h- ]等等。
               
& _. i( T0 N, Q. ], K1 f解决方案:
9 t$ o) K. W* r$ ^* ^1 j/ r+ C               
+ b0 i% U, L1 t  r0 y
) @* K4 N4 [5 `6 Q6 ]
' i! {0 j8 N7 S                WITH basedata(id,keywords) AS
* e+ U& X4 i2 q3 g" b    (
    SELECT 1,’cat, dog, man, mouse’ union all
6 a" {! N9 ~  ]) z2 j0 C    SELECT 2 ,’man, pen, pencil, eraser’ union all
6 m& S  j+ d+ Y  x    SELECT 3,’dog, man, friends’ union all
    SELECT 4,’dog, leash’
    ),
. x5 y; n- I* U: @% I" |; P    cte(id, t, x)
* r, G. ?6 j: v0 ^, ~& H) a  y         AS (SELECT *,
6 o' X, g, J& q  `( L# T                    CAST(‘‘ + REPLACE(keywords,’,’,’‘) + ‘‘ AS XML)
+ X% @$ L; J0 w2 \* k( t             FROM   basedata)
; F9 ^% P) E2 Y3 A    SELECT id,
           LTRIM(RTRIM(w.value(‘.’, ‘nvarchar(max)’))) as keyword
$ L) o7 }. Q6 w3 E6 H) d    INTO #Split     
8 Y% G" `. `5 ~5 j$ D    FROM   cte
           CROSS APPLY x.nodes(‘//foo’) as word(w)
DECLARE @ColList nvarchar(max)
* A& V  Y# C3 [* w! H* {SELECT @ColList = ISNULL(@ColList + ',','') + keyword
1 O2 H5 U7 I; T+ L3 ^' |; SFROM (
SELECT DISTINCT QUOTENAME(keyword) AS keyword
, q  C+ L. m  Q5 z- k5 g1 z" b3 \FROM #Split
) T
EXEC(N'      
2 p( d3 F' A3 I9 n, |0 S* w+ h6 iSELECT *
FROM #Split
PIVOT (COUNT(keyword) FOR keyword IN (' + @ColList + N')) P')
DROP TABLE #Split
给
id          cat         dog         eraser      friends     leash       man         mouse       pen         pencil
# O+ q% w9 U) B7 I* ?----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
; o: d& M; H/ [: e- o1           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