SQL Server Group Concat with Different characters
- by Molloch
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 ''?