CODEWITHSUNDEEP
mysql
Sanju Menon
Sanju Menon

Reputation: 729

MYSQL Query records with same id in Comma

Iam trying to write a mysql query. My table structure is as below:

===========================
tender_id | supplier_name
===========================
 160001       Tata
 160005       Wilsons Limited
 160008       Tilkom LTD
 160008       DULUB LLC
 160008       DULUB LLC
============================

Now i need a help with a query which should produce the below result that is when ever a tender_id has got more than one supplier_name then it should come with a comma. Iam not sure if i can achieve this with a query. i know to get this result from php script but just wanna know if i can do this with a mysql query:

====================================
tender_id | supplier_name
====================================
 160001      Tata
 160005      Wilsons Limited
 160008      Tilkom LTD, DULUB LLC
====================================

I started with this query below:

SELECT tender_id, supplier_name FROM `quotation_items` group by tender_id, supplier_name

Upvotes: 0

Views: 743

Answers (3)

Andrinux
Andrinux

Reputation: 374

SELECT * FROM my_table;

Result:

+-----------+-------------------+
| tender_id | supplier_name     |
+-----------+-------------------+
| 160001    | Tata              |
| 160005    | Wilsons Limited   |
| 160008    | Tilkom LTD        |
| 160008    | DULUB LLC         |
+-----------+-------------------+

Try this:

SELECT 
    tender_id, 
    GROUP_CONCAT(supplier_name SEPARATOR ", ") concated_field
FROM my_table
GROUP BY tender_id;

Result:

+-----------+-------------------------+
| tender_id | concated_field           |
+-----------+-------------------------+
| 160001    | Tata                    |
| 160005    | Wilsons Limited         |
| 160008    | Tilkom LTD, DULUB LLC   |
+-----------+-------------------------+

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

You should be doing a GROUP BY on the tender_id column alone, and using GROUP_CONCAT to aggregate suppliers into a CSV list:

SELECT tender_id, GROUP_CONCAT(DISTINCT supplier_name) AS supplier_name
FROM quotation_items
GROUP BY tender_id

Note that the default separator for GROUP_CONCAT is comma, but if you wanted a different one you could specify it using SEPARATOR.

Demo here:

SQLFiddle

Upvotes: 0

Hermanto
Hermanto

Reputation: 552

You need to use GROUP CONCAT. Your query should be like this:

SELECT tender_id, GROUP_CONCAT(DISTINCT supplier_name SEPARATOR ', ')
FROM `quotation_items` 
GROUP BY tender_id;

To avoid duplicate names, just add DISTINCT keyword in GROUP_CONCAT function.

Upvotes: 2

Related Questions