Build a report with SQL with combining columns and rows?

Question

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.

Which dbms are you using? (The answer might be product specific.) — jarlh, Apr 09 '21 at 08:40

score 0 · Answer 1 · edited Apr 09 '21 at 11:14

0

You can use a query like this:

SELECT
  Cust, STRING_AGG(Product + '_' + CAST(QTY AS varchar(10)), ', ') AS PURC, Date 
FROM PURC_Table 
GROUP BY Cust, Date

Note: STRING_AGG() is supported on SQL Server 2017 and later.

edited Apr 09 '21 at 11:14

Dale K

25,246
15
42
71

answered Apr 09 '21 at 09:36

hsn

368
2
9

score 0 · Accepted Answer · answered Apr 09 '21 at 09:56

0

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]

answered Apr 09 '21 at 09:56

Stu

30,392
6
14
33

This is perfect. Exactly what I needed. Thank you everyone for the assistance. – KojTug Apr 09 '21 at 20:32

score 0 · Answer 3 · answered Apr 09 '21 at 11:15

The first column seems to be based on the minimum purc_id. For the concatenated column, the other answers are fine, but concat() is more convenient:

select row_number() over (order by min(purc_id)) as rep_id
       cus_id as Cust,
       string_agg(concat(product, '_', qty), ', ') as purc,
       [Date]
from PurchaseReport
group by cus_id, [Date]

Build a report with SQL with combining columns and rows?

3 Answers3