How To Get Multiple Values In Single Column In Mysql

In this tutorial, you will learn how to retrieve multiple values within a single column using MySQL. This is a helpful technique when you want to organize and display data in a more efficient and user-friendly manner.

By the end of this tutorial, you should be able to use MySQL queries to retrieve multiple values in a single column and display them in a neat and organized format. Let’s get started!

Step 1: Set Up The Database Table

First, let’s set up a sample database table to work with. We will create a table named ‘orders’ with the following structure:

1
2
3
4
5
6
CREATE TABLE orders (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(255),
  product_name VARCHAR(255),
  quantity INT(11)
);

Next, insert some sample data into the ‘orders’ table:

1
2
3
4
5
6
INSERT INTO orders (customer_name, product_name, quantity) VALUES
  ('John', 'Laptop', 1),
  ('John', 'Mouse', 1),
  ('John', 'Keyboard', 1),
  ('Jane', 'Laptop', 1),
  ('Jane', 'Headset', 1);

Step 2: Use the GROUP_CONCAT Function

To retrieve multiple values in a single column, we will use the GROUP_CONCAT function. The GROUP_CONCAT function aggregates multiple values from the specified column into a single string, separated by a delimiter such as a comma. Here’s a sample SQL query:

1
2
3
SELECT customer_name, GROUP_CONCAT(product_name) AS ordered_products
FROM orders
GROUP BY customer_name;

This query will display the customer_name and ordered_products columns, where the ordered_products column contains a list of product names separated by a comma, retrieved by the GROUP_CONCAT function.

Step 3: Customize the Delimiter (Optional)

By default, the GROUP_CONCAT function uses a comma as the delimiter to separate values. However, you can customize the delimiter by specifying the SEPARATOR keyword. For example:

1
2
3
SELECT customer_name, GROUP_CONCAT(product_name SEPARATOR '; ') AS ordered_products
FROM orders
GROUP BY customer_name;

In this query, we used the semicolon followed by a space (‘; ‘) as a separator instead of the default comma, to separate the product names in the ordered_products column.

Step 4: Execute the Query and Display the Output

Now, execute the SQL query and display the output. The result should look like this:

As you can see, the output displays the customer name and a list of ordered products within a single column, separated by the specified delimiter.

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE orders (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(255),
  product_name VARCHAR(255),
  quantity INT(11)
);
 
INSERT INTO orders (customer_name, product_name, quantity) VALUES
  ('John', 'Laptop', 1),
  ('John', 'Mouse', 1),
  ('John', 'Keyboard', 1),
  ('Jane', 'Laptop', 1),
  ('Jane', 'Headset', 1);
 
SELECT customer_name, GROUP_CONCAT(product_name) AS ordered_products
FROM orders
GROUP BY customer_name;
 
SELECT customer_name, GROUP_CONCAT(product_name SEPARATOR '; ') AS ordered_products
FROM orders
GROUP BY customer_name;

Conclusion

In this tutorial, you learned how to use the GROUP_CONCAT function in MySQL to retrieve multiple values within a single column. This can be useful for organizing and displaying data in a more efficient and user-friendly manner.

Remember that you can customize the delimiter used by the GROUP_CONCAT function by specifying the SEPARATOR keyword. Happy querying!