Concatenate duplicates within table column

Question

I have a query that is returning data similar to the following:

po_line.line_no (PO Line #)	oe_line_po.order_number (Sales order #)
1	A
1	B
1	C
2	D
3	E
3	F
4	null

Some of our po_line.line_no values correspond to multiple oe_line_po.order_number values (could be any number of sales orders), and when we order extra stock they don't correspond to a oe_line_po.order_number value at all. Is there any way that I can concatenate them in the SQL definition to avoid creating multiple rows for each po_line.line_no that has multiple oe_line_po.order_number values? I'm looking to produce something like this:

po_line.line_no (PO Line #)	oe_line_po.order_number (Sales order #)
1	A,B,C
2	D
3	E,F
4	null

The goal is to prevent having to create a separate query and use a lookup in Excel. Here's the definition for reference, since some of the joins are left joins! The problem is also tied to oe_hdr.po_no and oe_line.extended_desc since those are also fields tied to the sales order.

SELECT
    po_hdr.po_type AS 'Disposition',
    contacts.first_name AS 'Buyer Name',
    po_hdr.order_date AS 'PO Date',
    po_hdr.po_no AS 'PO Number',
    po_line.line_no AS 'PO Line Number',
    po_hdr.external_po_no AS 'PO External PO #',
    oe_hdr.po_no AS 'SO External PO #',
    po_hdr.supplier_id AS 'Supplier ID',
    supplier.supplier_name AS 'Supplier Name',
    supplier.average_lead_time AS 'Supplier Average Lead Time',
    inv_mast.item_id AS 'Item ID',
    po_line.item_description AS 'Item Description',
    po_line.extended_desc AS 'PO Extended Description',
    oe_line.extended_desc AS 'SO Extended Description',
    inv_mast.extended_desc AS 'Item Extended Description',
    po_line.unit_price AS 'Unit Cost',
    po_line.unit_price AS 'Unit Price', po_line.qty_ordered AS 'Quantity Ordered',
    po_line.qty_received AS 'Quantity Received',
    supplier_ud.where_to_check AS 'Primary Rep Email',
    supplier_ud.customer_service_email AS 'Status Email',
    supplier_ud.online_tracking AS 'Online Tracking?',
    supplier_ud.online_status AS 'Online Status URL',
    supplier_ud.username_pw AS 'Username/PW'
FROM
    P21.dbo.contacts contacts,
    P21.dbo.inv_mast inv_mast,
    P21.dbo.po_hdr po_hdr,
    P21.dbo.supplier supplier,
    P21.dbo.supplier_ud supplier_ud,
    P21.dbo.po_line po_line
    LEFT JOIN P21.dbo.oe_line_po oe_line_po
        ON po_line.line_no = oe_line_po.po_line_number
            AND (oe_line_po.po_no = po_line.po_no)
    LEFT JOIN P21.dbo.oe_line oe_line
        ON oe_line_po.order_number = oe_line.order_no
            AND (oe_line_po.line_number = oe_line.line_no)
    LEFT JOIN P21.dbo.oe_hdr oe_hdr
        ON oe_line.order_no = oe_hdr.order_no   
WHERE 
    po_hdr.po_no = po_line.po_no AND 
    po_hdr.supplier_id = supplier.supplier_id AND 
    po_hdr.requested_by = contacts.id AND 
    inv_mast.inv_mast_uid = po_line.inv_mast_uid AND
    supplier_ud.supplier_id = supplier.supplier_id
    AND 
        ((po_line.cancel_flag='N') AND
        (po_line.complete='N') AND
        (po_hdr.location_id<>20) AND
        (po_hdr.order_date<(GETDATE()-0)) AND
ORDER BY 
    supplier.supplier_name, po_hdr.po_no, po_line.line_no

SO, you gave us what is expected, and your query; what did your query return? — Mark Stewart, Nov 05 '21 at 23:15

score 0 · Answer 1 · answered Nov 05 '21 at 21:17

For grouped concatenation you can refer this article here

select @@version;
create table #table(
po_sales_order int,
sales_order varchar(10))
insert into #table
select 1,'A'
UNION ALL
SELECT 1, 'B'
UNION ALL 
SELECT 1,'C'
UNION ALL
SELECT 2,'D'
UNION ALL
SELECT 3,'E'
UNION ALL
SELECT 3,'F'
UNION ALL
SELECT 4,NULL
SELECT po_sales_order, sales_order = STUFF((SELECT N', ' + sales_order 
  FROM #table AS p2
   WHERE p2.po_sales_order = p.po_sales_order 
   ORDER BY po_sales_order
   FOR XML PATH(N'')), 1, 2, N'')
FROM #table AS p
GROUP BY po_sales_order
ORDER BY po_sales_order;

db_fiddle here

You can refer the above code and use it to your existing code.

Concatenate duplicates within table column

1 Answers1