How to select different values from same column and display them in different columns with MySQL?

To select different values on the basis of condition, use CASE statement. Let us first create a table −

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name varchar(40),
Score int
) ;
Query OK, 0 rows affected (0.54 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(Name,Score) values('Chris',45);
Query OK, 1 row affected (0.24 sec)
mysql> insert into DemoTable(Name,Score) values('David',68);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(Name,Score) values('Robert',89);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable(Name,Score) values('Bob',34);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(Name,Score) values('Sam',66);
Query OK, 1 row affected (0.22 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----+--------+-------+
| Id | Name   | Score |
+----+--------+-------+
|  1 | Chris  |    45 |
|  2 | David  |    68 |
|  3 | Robert |    89 |
|  4 | Bob    |    34 |
|  5 | Sam    |    66 |
+----+--------+-------+
5 rows in set (0.00 sec)

Following is the query to select different values from same column −

mysql> select Score,
   case when Score < 40 then Score end as ' Score Less than 40',
   case when Score between 60 and 70 then Score end as 'Score Between 60 and 70 ',
   case when Score > 80 then Score end as 'Score greater than 80'
   from DemoTable;

This will produce the following output −

+-------+--------------------+--------------------------+-----------------------+
| Score | Score Less than 40 | Score Between 60 and 70  | Score greater than 80 |
+-------+--------------------+--------------------------+-----------------------+
|    45 |               NULL |                     NULL |                  NULL |
|    68 |               NULL |                       68 |                  NULL |
|    89 |               NULL |                     NULL |                    89 |
|    34 |                 34 |                     NULL |                  NULL |
|    66 |               NULL |                       66 |                  NULL |
+-------+--------------------+--------------------------+-----------------------+
5 rows in set, 1 warning (0.03 sec)

AmitDiwan

Published on 09-Oct-2019 11:35:59

• Related Questions & Answers
• MySQL query to separate and select string values (with hyphen) from one column to different columns
• Concatenate two values from the same column with different conditions in MySQL
• Select distinct values from three columns and display in a single column with MySQL
• How to select and display a list of values in one column that are available in two different MySQL columns?
• Count only null values in two different columns and display in one MySQL select statement?
• Display two different columns from two different tables with ORDER BY?
• Select multiple sums with MySQL query and display them in separate columns?
• How to display two different sums of the same price from column Amount in MySQL?
• Count values based on conditions and display the result in different columns with MySQL?
• Find duplicate column values in MySQL and display them
• Select values that meet different conditions on different rows in MySQL?
• Copy from one column to another (different tables same database) in MySQL?
• Select count of values (Yes, No) with same ids but different corresponding records in MySQL?
• Concatenate columns from different tables in MySQL
• Get the time difference between values in different columns with MySQL