第一 concate 两列然后做 string_agg . 这是演示。

with cte as
(
  select
    mid,
    concat(pid, '(', quantity, ')') as concat_col
  from table1
)

select
  row_number() over (order by mid) as id,
  mid,
  string_agg(concat_col, ', ') as final
from cte
group by
  mid

输出：

| id  | mid | final       |
| --- | --- | ----------- |
| 1   | 1   | 2(3), 3(10) |
| 2   | 2   | 2(11), 1(5) |

如果您使用的是旧版本的sql server，请尝试以下操作

with cte as
(
  select
    mid,
    concat(pid, '(', quantity, ')') as concat_col
  from table1
)

select
  row_number() over (order by mid) as id,
  mid,
  stuff((
  select ',' + concat_col
  from cte c1
  where c.mid = c1.mid
  for XML PATH('')
  ), 1, 1, '') as final
from cte c
group by
  mid

select MID, string_agg(concat(PID, '(', Quantity,')'), ', ')
    from dbo.Sample 
    group by MID

Result : 
    MID FINAL
    1   2(3), 3(10)
    2   2(11), 1(5)