convert rows to column in mysql

Question

------------------------------------------------------------
courseid|course    |coursedesc                             |
------------------------------------------------------------
 1      | BSIT     |  Bachelor of science in info tech     |
 2      | BSCS     |   Bachelor of science in comp sci     |
 3      | BSHRM    |  Bachelor of science in hotel & res   |

I want to convert it to something like this dynamically

---------------------------------
|course1 | course2   | course3  |
---------------------------------
 BSIT     | BSCS     | BSCS     |

I have this code and giving me Result :

MySQL returned an empty result set (i.e. zero rows).

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when course = ''',
      course,
      ''' then 1 end) AS ',
      replace(course, ' ', '')
    )
  ) INTO @sql
from tbl_course;

SET @sql = CONCAT('SELECT course, ', @sql, ' from tbl_course

group by course');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Please stop shouting, and consider handling display issues in application code — Strawberry, Sep 13 '17 at 07:18
Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — Alexander, Sep 13 '17 at 07:24

Ullas · Answer 1 · 2017-09-13T07:35:39.927

0

Query

set @query = null;
select
  group_concat(distinct
    concat(
      'max(case when `courseid` = ',
      `courseid`, ' then `course` end) as `course', `courseid`, '`'
    )
  ) into @query
from `tbl_course`;

set @query = concat('select ', @query, ' from `tbl_course` ');

prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

Find a demo here

edited Sep 13 '17 at 07:35

answered Sep 13 '17 at 07:27

Ullas

11,450
4
33
50

It gives me the same result # MySQL returned an empty result set (i.e. zero rows). @Wanderer – Mark Lopez Sep 13 '17 at 07:39
@MarkLopez : Is there any null values in any of those 2 columns? – Ullas Sep 13 '17 at 07:39
None sir @Wanderer – Mark Lopez Sep 13 '17 at 07:49

convert rows to column in mysql

MySQL returned an empty result set (i.e. zero rows).

1 Answers1