SQL Server组Concat具有不同的字符

制作更具人性化的解决方案

抱歉，这是我能根据您的要求做的最好的事情。

SQL小提琴

MS SQL Server 2008架构设置 ：

create table YourTable(  ParentID int,  ChildName varchar(10));insert into YourTable values(1, 'Max'),(1, 'Jessie'),(2, 'Steven'),(2, 'Lucy'),(2, 'Jake'),(3, 'Mark');

查询1 ：

with T as (  select ParentID,         ChildName,         row_number() over(partition by ParentID order by ChildName) as rn,         count(*) over(partition by ParentID) as cc  from YourTable)select T1.ParentID,       (         select case       when T2.rn = 1 and T2.cc > 1 then ' and '       else ', '      end + T2.ChildName         from T as T2         where T1.ParentID = T2.ParentID         order by T2.rn desc         for xml path(''), type       ).value('substring(text()[1], 3)', 'varchar(max)') as ChildNamesfrom T as T1group by T1.ParentID

结果 ：

| PARENTID | CHILDNAMES |------------------------------------|        1 |        Max and Jessie ||        2 | Steven, Lucy and Jake ||        3 |       Mark |