SQL Server Group Concat with Different characters

Question

I have looked through a number of solutions to emulating "Group concat" functionality in SQL Server. I wanted to make a more human readable solution though and I can't work out how to do it.

I have a view:

ParentID | ChildName

Which contains the records, for example:

1 | Max
1 | Jessie
2 | Steven
2 | Lucy
2 | Jake
3 | Mark

I want to "Group Concat" these to get:

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

So If there is only 1 child, just return name, if there are more than one, concat the last 2 with an ' and ' and all others with a ', '.

I am a bit stuck on how to do this without resorting to CLR, which I don't want to do. I am happy with a function - but speed is an issue and how do I determine the child number so I can choose between ' and ', ', ' or ''?

Answer 1

make a more human readable solution

Sorry, this is the best I can do with your requirement.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  ParentID int,
  ChildName varchar(10)
);

insert into YourTable values
(1, 'Max'),
(1, 'Jessie'),
(2, 'Steven'),
(2, 'Lucy'),
(2, 'Jake'),
(3, 'Mark');

Query 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 ChildNames
from T as T1
group by T1.ParentID

Results:

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