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)
Published on 09-Oct-2019 11:35:59
