Mysql – How to transpose/convert rows as columns in thesql

MySQL

I have table structures like below
Subjects Table

      +----+------+------+--------+---------+
      | id | C_Id | G_Id | status | subject |
      +----+------+------+--------+---------+
      |  1 |   13 |    4 |      1 | Telugu  |
      |  2 |   13 |    4 |      1 | Hindi   |
      |  3 |   13 |    4 |      1 | English |
      |  4 |   13 |    4 |      1 | Maths   |
      |  5 |   13 |    4 |      1 | Physics |
      |  6 |   13 |    4 |      1 | Biology |
      |  7 |   13 |    4 |      1 | Social  |
      +----+------+------+--------+---------+ 

      +----+-----+-----+--------+--------+-------+-------+-------+
      | id | Cid | Gid | Examid | rollno | subId | Marks | paper |
      +----+-----+-----+--------+--------+-------+-------+-------+
      |  1 |  13 |   4 |      1 |      1 |     1 | 14.50 |     1 |
      |  2 |  13 |   4 |      1 |      2 |     1 | 12.00 |     1 |
      |  3 |  13 |   4 |      1 |      1 |     2 | 13.00 |     1 |
      |  4 |  13 |   4 |      1 |      2 |     2 | 15.00 |     1 |
      |  5 |  13 |   4 |      1 |      1 |     3 | 16.00 |     1 |
      |  6 |  13 |   4 |      1 |      2 |     3 | 18.00 |     1 |
      |  7 |  13 |   4 |      1 |      1 |     4 | 19.00 |     1 |
      |  8 |  13 |   4 |      1 |      2 |     4 | 23.00 |     1 |
      |  9 |  13 |   4 |      1 |      1 |     5 | 21.00 |     1 |
      | 10 |  13 |   4 |      1 |      2 |     5 | 24.00 |     1 |
      | 11 |  13 |   4 |      1 |      1 |     6 | 20.00 |     1 |
      | 12 |  13 |   4 |      1 |      2 |     6 | 19.00 |     1 |
      | 13 |  13 |   4 |      1 |      1 |     7 | 20.00 |     1 |
      | 14 |  13 |   4 |      1 |      2 |     7 | 21.00 |     1 |
      | 15 |  13 |   4 |      2 |      1 |     1 | 45.00 |     2 |
      | 16 |  13 |   4 |      2 |      2 |     1 | 40.00 |     2 |
      | 17 |  13 |   4 |      2 |      1 |     1 | 32.00 |     3 |
      | 18 |  13 |   4 |      2 |      2 |     1 | 33.00 |     3 |
      | 19 |  13 |   4 |      2 |      1 |     2 | 80.00 |     1 |
      | 20 |  13 |   4 |      2 |      2 |     2 | 89.00 |     1 |
      | 21 |  13 |   4 |      2 |      1 |     3 | 39.00 |     2 |
      | 22 |  13 |   4 |      2 |      2 |     3 | 38.00 |     2 |
      | 23 |  13 |   4 |      2 |      1 |     3 | 41.00 |     3 |
      | 24 |  13 |   4 |      2 |      2 |     3 | 45.00 |     3 |
      | 25 |  13 |   4 |      2 |      1 |     4 | 34.00 |     2 |
      | 26 |  13 |   4 |      2 |      2 |     4 | 38.00 |     2 |
      | 27 |  13 |   4 |      2 |      1 |     4 | 32.00 |     3 |
      | 28 |  13 |   4 |      2 |      2 |     4 | 33.00 |     3 |
      | 29 |  13 |   4 |      2 |      1 |     5 | 31.00 |     1 |
      | 30 |  13 |   4 |      2 |      2 |     5 | 34.00 |     1 |
      | 31 |  13 |   4 |      2 |      1 |     6 | 33.00 |     1 |
      | 32 |  13 |   4 |      2 |      2 |     6 | 31.00 |     1 |
      | 33 |  13 |   4 |      2 |      1 |     7 | 35.00 |     2 |
      | 34 |  13 |   4 |      2 |      2 |     7 | 31.00 |     2 |
      | 35 |  13 |   4 |      2 |      1 |     7 | 43.00 |     3 |
      | 36 |  13 |   4 |      2 |      2 |     7 | 38.00 |     3 |
      +----+-----+-----+--------+--------+-------+-------+-------+

So Far I have wrote the code

select ta.rollno,
ta.StdNm,
max(case when s.subject = 'Telugu' AND tm.paper=1 then tm.Marks end) Telugu,
max(case when s.subject = 'Telugu' AND tm.paper=2 then tm.Marks end) Telugu1,
max(case when s.subject = 'Telugu' AND tm.paper=3 then tm.Marks end) Telugu2,
max(case when s.subject = 'Hindi' AND tm.paper=1 then tm.Marks end) Hindi,
max(case when s.subject = 'Hindi' AND tm.paper=2 then tm.Marks end) Hindi1,
max(case when s.subject = 'Hindi' AND tm.paper=3 then tm.Marks end) Hindi2,
max(case when s.subject = 'English' AND tm.paper=1 then tm.Marks end) English,
max(case when s.subject = 'English' AND tm.paper=2 then tm.Marks end) English1,
max(case when s.subject = 'English' AND tm.paper=3 then tm.Marks end) English2,
max(case when s.subject = 'Maths' AND tm.paper=1 then tm.Marks end) Maths,
max(case when s.subject = 'Maths' AND tm.paper=2 then tm.Marks end) Maths1,
max(case when s.subject = 'Maths' AND tm.paper=3 then tm.Marks end) Maths2,
max(case when s.subject = 'Physics' AND tm.paper=1 then tm.Marks end) Physics,
max(case when s.subject = 'Physics' AND tm.paper=2 then tm.Marks end) Physics1,
max(case when s.subject = 'Physics' AND tm.paper=3 then tm.Marks end) Physics2,
max(case when s.subject = 'Biology' AND tm.paper=1 then tm.Marks end) Biology,
max(case when s.subject = 'Biology' AND tm.paper=2 then tm.Marks end) Biology1,
max(case when s.subject = 'Biology' AND tm.paper=3 then tm.Marks end) Biology2,
max(case when s.subject = 'Social' AND tm.paper=1 then tm.Marks end) Social,
max(case when s.subject = 'Social' AND tm.paper=2 then tm.Marks end) Social1,
max(case when s.subject = 'Social' AND tm.paper=3 then tm.Marks end) Social2
FROM tbl_cmarks tm
INNER JOIN tbl_classes tc
 ON tm.Cid = tc.C_Id
INNER JOIN tbl_admission ta
 ON ta.rollno = tm.rollno
INNER JOIN tbl_subjects s
 on tm.subId = s.id
where tm.Cid = 13 
and tm.Examid=2
group by  ta.rollno

Here in my script i am passing the subjects Names as hard Coded But in future we don't know how many subjects will have in Class 13
where should i change in my query to pass the subject Ids instead of Subject Names and that too not as hard coded.

Can i take all subject Ids for Particular Class into a variable and pass in the Max(Case) statement.? Is it possible?

Please review my code and give me a clue/help, to do work this code.

Thank you in advance.

Best Answer

In order to do that you have to use dynamic SQL.

To simplify things on the client side it's better to wrap it in a stored procedure. In your case such procedure might look like

DELIMITER $$
CREATE PROCEDURE sp_exam(IN _cid INT, IN _examid INT)
BEGIN
    SET SESSION group_concat_max_len = (7 * 1024);

    SET @sql = NULL;

    SELECT GROUP_CONCAT(DISTINCT
             CONCAT(
               'MAX(CASE WHEN m.subid = ', subid,
               ' AND m.paper = ', paper, 
               ' THEN m.marks END) ', subject, paper))
      INTO @sql
      FROM tbl_cmarks m JOIN tbl_subjects s 
        ON m.subid = s.id
     WHERE cid = _cid
       AND examid = _examid;

    SET @sql = CONCAT(
                 'SELECT a.rollno, a.stdnm, ', @sql,  
                  ' FROM tbl_cmarks m JOIN tbl_admission a
                      ON m.rollno = a.rollno 
                   WHERE m.cid = ', _cid, 
                   ' AND m.examid = ', _examid,
                 ' GROUP BY a.rollno, a.stdnm');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Note: JOINs with tbl_classes and tbl_subjects have been removed since you don't fetch anything from the former and you don't need later for conditional grouping (ids used instead).

Then on client side you just do

CALL sp_exam(13, 2);

Sample output:

| ROLLNO |     STDNM | TELUGU2 | TELUGU3 | HINDI1 | ENGLISH2 | ENGLISH3 | MATHS2 | MATHS3 | PHYSICS1 | BIOLOGY1 | SOCIAL2 | SOCIAL3 |
-------------------------------------------------------------------------------------------------------------------------------------
|      1 | Student 1 |      45 |      32 |     80 |       39 |       41 |     34 |     32 |       31 |       33 |      35 |      43 |
|      2 | Student 2 |      40 |      33 |     89 |       38 |       45 |     38 |     33 |       34 |       31 |      31 |      38 |

Here is SQLFiddle demo

SUGGESTION

At first glance, the query you need generated looks like this

SELECT
    advertiser_id,
    IF(activity_type=1,counts,0) activity_type1,
    IF(activity_type=2,counts,0) activity_type2,
    IF(activity_type=3,counts,0) activity_type3
FROM ceciliadb.tbl.tmp_act
GROUP BY advertiser_id;

I would craft it like this:

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since you have 100 types, you nned to wedge those 100 type IDs in the subquery

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since the generated query may be too long, set the GROUP_CONCAT length to 10 MB beforehand

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

If your columns are not sequential in nature, then use the actual values as headers

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT DISTINCT activity_type FROM ceciliadb.tbl.tmp_act) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Mysql – Transpose MySQL rows as column names – DYNAMIC

I worked it out, in-case anyone would like to know the following Query did it:

SELECT CONCAT(
'SELECT `ost_ticket_event`.ticket_id', GROUP_CONCAT('
 ,    `t_', REPLACE(state, '`', '``'), '`.timestamp
     AS `', REPLACE(state, '`', '``'), '`'
 SEPARATOR ''),
' FROM `ost_ticket_event` ', GROUP_CONCAT('
 LEFT JOIN `ost_ticket_event`   AS `t_', REPLACE(state, '`', '``'), '`
        ON `ost_ticket_event`. ticket_id = `t_', REPLACE(state, '`', '``'), '`.ticket_id
       AND `t_', REPLACE(state, '`', '``'), '`.state = ', QUOTE(state)
 SEPARATOR ''),
' GROUP BY `ost_ticket_event`.ticket_id'
) INTO @qry FROM (SELECT DISTINCT state FROM `ost_ticket_event`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

Best Answer

Related Solutions

MySQL Rows to Columns – How to Change Rows to Columns

SUGGESTION

Mysql – Transpose MySQL rows as column names – DYNAMIC

Related Question