Create Table dbo.T1
(ID INT Identity, Province Varchar(80), City Varchar(80),
Constraint PK_T1 Primary Key (ID));
Insert INTO dbo.T1 (Province,City) values ('广东','广州'),('广东','东莞'),('广东','深圳'),('四川','成都'),('四川','南充'),('四川','达州'),('四川','绵阳');
Insert dbo.T1 (Province,City) Values ('广东',NULL);
Select Province,String_AGG(City,',') as Citys
From dbo.T1 Group By Province;
-------------------------------------
Province
Citys
广东
广州,东莞,深圳
四川
成都,南充,达州,绵阳
-------------------------------------
Select Translate('ab[cd]e{fg}','[]{}','()()') as S,'*'+TRIM('!x,. ' FROM 'xxabcd .!,')+'*' as S2,CONCAT_WS(',','abcd','xxx','yyy',null,'zzz') as S3;
--------------------------------------
S
S2
S3
ab(cd)e(fg)
*abcd*
abcd,xxx,yyy,zzz
--------------------------------------
终于加上了String_AGG，不再FOR XML，自动忽略NULL值，接地气
MS原贴参考：
https://msdn.microsoft.com/en-US/library/mt788653(SQL.130).aspx |
