Build a report with SQL with combining columns and rows?

Here is my table:

Table Purchase Report

PURC_ID	CUS_ID	Product	QTY	Date
1	John	Leg_01	2	2021-04-09
2	John	Head_01	1	2021-04-09
3	Dawn	Head_01	1	2021-04-09
4	Dawn	Shoulder_01	2	2021-04-09
5	Dawn	Leg_01	1	2021-04-09
6	Keith	Leg_01	2	2021-04-09

I would like to build the report as follow:

Table 4: (PURC table will combine with other columns. I need Product Code follow by underscore follow by qty).

REP_ID	Cust	PURC	Date
1	John	Head_01_1, Leg_01_2	2021-04-09
2	Dawn	Head_01_1, Shoulder_01_2, Leg_01_1	2021-04-09
3	Keith	Leg_01_2	2021-04-09

I know how to join table, but I’m not sure how to combine in this format. Any help is much appreciated.

Answer

The following should give you the desired output

select row_number() over(order by avg(purc_id)) REP_ID,
  CUS_ID Cust,
  string_agg(product + '_' + cast(Qty as varchar(2)),', ') PURC,
  [Date]
from PurchaseReport
group by CUS_ID, [Date]