I want row data of same id in one single row

The Table Format is like

ID | MID | PID |   Quantity
1  |  1  |   2 |      3
2  |  1  |   3 |     10
3  |  2  |   2 |     11
4  |  2  |   1 |      5

I want to result as following

ID | MID |     Final
1  |  1  |   2(3),3(10)
2  |  2  |   2(11),1(5)

Answer

first concate two columns and then do string_agg. Here is the demo.

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

output:

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

If you are using older version of SQL Server then try the following

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