SqlServer模仿Mysql函数Find_in_set,group_concat的功能

判断某个逗号隔开的字段中有某个值

FIND_IN_SET('a','a,b,c,d')CHARINDEX(','+'b'+',' , ','+'a,b,c'+',') > 0 替代

模仿mysql的group_concat的示例

1
2
3
4
5
6
7
SELECT id,     
val=( SELECT [value] +','
FROM tb AS b
WHERE b.id = a.id
FOR XML PATH('') )
FROM tb AS a
GROUP BY id
1
2
3
4
5
6
7
SELECT id,     
[val]=STUFF( (SELECT ','+[value]
FROM tb AS b
WHERE b.id = a.id
FOR XML PATH('')) , 1 , 1 , '' )
FROM tb AS a
GROUP BY id