Question 20728 in Mysql Question posted in
The official MySQL documentation can be found here.

how to merge 3 rows with the same data except 1 column? – Mysql

my output is look like:

id order status item supplier confirm date customer Manager
555222 approved 12421 Hawi 12/02/2023 0122 George
555222 approved 12421 Hawi 12/02/2023 0122 Michael
555222 approved 12421 Hawi 12/02/2023 0122 Tom
543677 approved 85332 Mint 27/01/2023 0324 John
200013 approved 90210 Mint 27/01/2023 0714 Brad
111111 approved 80765 Sweet 11/01/2023 0122 George
111111 approved 80765 Sweet 11/01/2023 0122 Michael
111111 approved 80765 Sweet 11/01/2023 0122 Tom

I was trying to merge all the managers of customer 0122 in one row by using group_concat and seperate, but i got struggle with the "group by" because i have more fields in the select (like 78 columns in the output).
i want to know if there is any way my query can use the "where" clause for searching all my customers if they have more then 1 manager and if there is, the query will do something to merge them?
***the manager column and the customer column are coming from different tables.
for example the output i would like to get:

id order status item supplier confirm date customer Manager
555222 approved 12421 Hawi 12/02/2023 0122 George, Michael, Tom
543677 approved 85332 Mint 27/01/2023 0324 John
200013 approved 90210 Mint 27/01/2023 0714 Brad
111111 approved 80765 Sweet 11/01/2023 0122 George, Michael, Tom
Tags:

2

Answers


  1. 0

    use group_concat() by using the group columns and take it 1st cte then in another select the rest of yours 72 columns and join with the 1st cte
    so sudo code will be like below

    with cte as (
    select idorder,status,item,supplier,confirmdate,customer,group_concat(manager)
    from table_name
    group by idorder,status,item,supplier,confirmdate,customer
),  select yours other column from table_name a join cte b on a.col1=b.col1 ..
    Login or Signup to reply.
  2. 0

    Presumably your are joining to customer and manager data somehow. So, you could GROUP BY the customer in a subquery before joining the subquery to your orders.

    This is a crude example, assuming that orders, customers and customer_managers come from different tables:

    SELECT *
FROM orders o
JOIN (
    SELECT c.*, GROUP_CONCAT(cm.manager SEPARATOR ', ') AS managers
    FROM customers c
    JOIN customer_managers cm
        ON c.id = cm.customer_id
    GROUP BY c.id
) cust
    ON o.customer_id = cust.id;

    If you have a vast number of customers and do not want to pre-aggregate the data for all of them, you could make the JOIN LATERAL:

    SELECT *
FROM orders o
JOIN LATERAL (
    SELECT c.*, GROUP_CONCAT(cm.manager SEPARATOR ', ') AS managers
    FROM customers c
    JOIN customer_managers cm
        ON c.id = cm.customer_id
    WHERE c.id = o.customer_id
    GROUP BY c.id
) cust;

    Here’s a db<>fiddle

    Hopefully, this serves as an example of how to apply it to your data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search