SQL Server team group with different characters

Question

I looked at a number of solutions for emulating the functionality of Group concat in SQL Server. I wanted to make a decision more understandable for a person, although I can’t understand how to do it.

I have a view:

ParentID | ChildName 

What contains the entries, for example:

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

I want to "Concat Group" to get:

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

So, if there is only one child, just return the name, if there are several, add the last 2 with "and" and all the others with ",".

I am a little fixated on how to do this without resorting to the CLR, which I don't want to do. I am pleased with the function, but speed is the problem and how to determine the child number so that I can choose between 'and', ',' or '??

Answer 1

make a decision more understandable for humans

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

SQL Fiddle

Setting up the MS SQL Server 2008 schema :

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

Request 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 | 

Answer 2

 select ParentID,STUFF((SELECT ' and '+ChildName FROM Table1 where ParentID=a.ParentID FOR XML PATH('')),1,4,'') as cnmae from Table1 a group by ParentID 

SQL FIDDLE DEMO

Answer 3

Good logical question. Please check the request below (the bit is long, but could not stop my posting of my little logic :)).

 CREATE TABLE #SampleTable ([ParentID] int, [ChildName] varchar(6)); INSERT INTO #SampleTable VALUES (1, 'Max') INSERT INTO #SampleTable VALUES (1, 'Jessie') INSERT INTO #SampleTable VALUES (2, 'Steven') INSERT INTO #SampleTable VALUES (2, 'Lucy') INSERT INTO #SampleTable VALUES (2, 'Jake') INSERT INTO #SampleTable VALUES (3, 'Mark') select * From #SampleTable ;WITH T(xParentID, xChildName, xChildNameResult, xC1, xC2)AS ( SELECT * FROM( SELECT ParentID , ChildName, CAST(ChildName AS NVARCHAR(MAX)) AS ChildNameResult, ROW_NUMBER() OVER (PARTITION BY [ParentID] ORDER BY ChildName) C1, COUNT(*) OVER (PARTITION BY [ParentID]) C2 FROM #SampleTable)x WHERE x.C1=1 UNION ALL SELECT ParentID, ChildName, CAST(T.xChildNameResult+(CASE WHEN C1=1 THEN '' WHEN C1=C2 THEN ' and ' ELSE ', ' END)+ChildName AS NVARCHAR(MAX)), C1, C2 FROM ( SELECT ParentID , ChildName, ROW_NUMBER() OVER (PARTITION BY ParentID order by ChildName) C1, COUNT(*) OVER (PARTITION BY ParentID) C2 FROM #SampleTable )y INNER JOIN T ON y.ParentID=T.xParentID and y.c1=T.xC1+1 )SELECT xParentID, xChildNameResult FROM T where xC1=xC2 OPTION (MAXRECURSION 0);