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.
Advertisement
Answer
The following should give you the desired output
x
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]