SQL limit join/concatenation result

Question

Table Master:

----------------------
| Name      | ID     |
----------------------
| A         | 1      |
| B         | 2      |
| C         | 3      |
----------------------

Table Detail:

----------------------
| masterID  | det     |
----------------------
| 1         | 21      |
| 1         | 31      |
| 1         | 442     |
| 1         | 76      |
| 2         | 1       |
| 2         | 90      |
| 3         | 48      |
| 3         | 56      |
| 3         | 109     |
----------------------

Requested result: Do a join and concatenation but limit the number of concatenated values to X (here 2) and if it's more then create a new row. for example the expected result from above will be:

----------------------
| Name      | dets   |
----------------------
| A         | 21, 31 |
| A         | 442, 76|
| B         | 1, 90  |
| C         | 48, 56 |
| C         | 109    |
----------------------

Using the below code I can get all the result concatenated however I need help about how to limit the number of records concatenated :

SELECT Master.Name, 
STUFF((
SELECT ','+Detail.det
FROM Detail
WHERE Master.ID = Detail.masterID
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'')
FROM Master

Also created the SQLFiddle for it : http://sqlfiddle.com/#!6/a1b69/1/0

Answer 1

Use row_number() and integer division to create a key that increments for every other row. Do that in a CTE and use the CTE in the main query and the for xml query.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table Master
(
  Name varchar(10),
  ID int
);

create table Detail
(
  masterID int,
  det varchar(10)
);

go

insert into Master values
('A', 1),
('B', 2),
('C', 3);

insert into Detail values
(1, 21 ),
(1, 31 ),
(1, 442),
(1, 76 ),
(2, 1  ),
(2, 90 ),
(3, 48 ),
(3, 56 ),
(3, 109);

Query 1:

with C as
(
  select M.Name,
         D.masterID,
         D.det,
         (row_number() over(partition by D.masterID order by D.det) - 1) / 2 as rn
  from dbo.Detail as D
    inner join dbo.Master as M
      on D.masterID = M.ID
)
select C.Name,
       stuff ((select ', ' + D.det
               from C as D
               where C.masterID = D.masterID and
                     C.rn = D.rn
               for xml path(''), type).value('text()[1]', 'varchar(max)'), 1, 2, '')
from C
group by C.masterID,
         C.Name,
         C.rn

Results:

| Name |         |
|------|---------|
|    A |  21, 31 |
|    A | 442, 76 |
|    B |   1, 90 |
|    C | 109, 48 |
|    C |      56 |