A value in a field that is separated by a comma
Find_in_set (' A ', ' a,b,c,d ') with CHARINDEX (', ' + ' B ' + ', ', ', ' + ' a,b,c ' + ', ') > 0 substitution
Ii. examples of group_concat that mimic MySQL
select id, val= ( select [value] + ', ' FROM tb AS b WHERE b.id = a.id for xml path (") ) from tb as a group by id
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
--Delete, create temporary table drop table #table1;select * into #table1from (Select myid, ' Auto ' as type) alldata--link the data by myID and then sort by the length of the linked data select * from ( select Myid, val=stuff ( (select ', ' +type from #table1 AS b WHERE b.myId = ta.myid for xml path (")) , 1 , 1 , " ) from #table1 ta group by myid) dd Order by len (Val) desc
This article is from the "Wind Trace _ Snow Tiger" blog, please be sure to keep this source http://snowtiger.blog.51cto.com/12931578/1929498
SQL Server simulates the function of MySQL function find_in_set,group_concat