Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Thank you @peterm Its working perfectly Thank you so much –  Narendar_CH Aug 12 '13 at 7:19
    
@Narendar_CH You're very welcome. I'm glad I could help :) –  peterm Aug 12 '13 at 7:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.