[Solved] filter table column and view the data in the another column [closed]

Question

I can’t explain this

Let me explain it for you.

You are looking for pivoting the comments values for the concern for each student. Unfortunately, MySQL has no pivot table operator.

However, you can use the CASE expression to do this. Like so:

SELECT 
  student_name,
  MAX(CASE WHEN concern = 'Academics'   THEN comments END) AS 'Accademics',
  MAX(CASE WHEN concern = 'Accomodation' THEN comments END) AS 'Accomodation'
FROM acad_concern    
GROUP BY student_name;

SQL Fiddle Demo

This will give you:

|  STUDENT_NAME |  ACCADEMICS | ACCOMODATION |
----------------------------------------------
|   Anne Curtis | this acad.. |  this is aco |
| benedict grey | also acad.. |  also accomo |

For multiple concerns and you don’t need to write them manually, you have to do this dynamically using dynamic SQL like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(concern = ''',
      concern, ''', comments, NULL)) AS ', '''', concern , '''')
  ) INTO @sql
FROM acad_concern;

SET @sql = CONCAT('SELECT student_name, ', @sql , '
    FROM acad_concern    
    GROUP BY student_name;');

prepare stmt 
FROM @sql;

execute stmt;

Updated SQL Fiddle Demo

Accepted Answer