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 a 3 tables like : 1) Tbl_subjects

      +----+------+-------------------+
      | id | C_Id |     subject       |
      +----+------+-------------------+
      |  1 |   13 | తెలుగు             |
      |  2 |   13 | हिन्दी              |
      |  3 |   13 | ENGLISH           |
      |  4 |   13 | MATHEMATICS       |
      |  5 |   13 | PHYSICAL SCIENCES |
      |  6 |   13 | BIOLOGY           |
      |  7 |   13 | SOCIAL STUDIES    |
      +----+------+-------------------+ 

2) tbl_masterexam

      +------+-----+--------------------+
      | exid | Cid |        exnm        |
      +------+-----+--------------------+
      |   64 |  13 | Unit Test-1        |
      |   65 |  13 | Unit Test-2        |
      |   66 |  13 | Quarterly Exams    |
      |   67 |  13 | Unit Test-3        |
      |   68 |  13 | Half yearly Exams  |
      |   69 |  13 | Unit Test-4        |
      |   70 |  13 | Pre - Public Exams |
      +------+-----+--------------------+

3) tbl_Cmarks

+-----+--------+--------+-------+-------+-------+
| Cid | Examid | rollno | subId | Marks | paper |
+-----+--------+--------+-------+-------+-------+
|  13 |     64 |      1 |     3 |    21 |     1 |
|  13 |     64 |      1 |     1 |    18 |     1 |
|  13 |     64 |      1 |     2 |    20 |     1 |
|  13 |     64 |      1 |     4 |    23 |     1 |
|  13 |     64 |      1 |     5 |    11 |     1 |
|  13 |     64 |      1 |     6 |    11 |     1 |
|  13 |     64 |      1 |     7 |    22 |     1 |
|  13 |     65 |      1 |     3 |    20 |     1 |
|  13 |     65 |      1 |     1 |    20 |     1 |
|  13 |     65 |      1 |     2 |    19 |     1 |
|  13 |     65 |      1 |     4 |    22 |     1 |
|  13 |     65 |      1 |     5 |    10 |     1 |
|  13 |     65 |      1 |     6 |    10 |     1 |
|  13 |     65 |      1 |     7 |    20 |     1 |
|  13 |     65 |      1 |     7 |    20 |     1 |
|  13 |     66 |      1 |     1 |    75 |     1 |
|  13 |     66 |      1 |     2 |    89 |     1 |
|  13 |     66 |      1 |     4 |    80 |     1 |
|  13 |     66 |      1 |     5 |    45 |     1 |
|  13 |     66 |      1 |     6 |    42 |     1 |
|  13 |     66 |      1 |     7 |    89 |     1 |
|  13 |     68 |      1 |     3 |    41 |     2 |
|  13 |     68 |      1 |     3 |    35 |     3 |
|  13 |     68 |      1 |     4 |    40 |     2 |
|  13 |     68 |      1 |     4 |    45 |     3 |
|  13 |     66 |      1 |     3 |    40 |     2 |
|  13 |     66 |      1 |     3 |    41 |     3 |
|  13 |     67 |      1 |     3 |    16 |     1 |
|  13 |     67 |      1 |     1 |    19 |     1 |
|  13 |     67 |      1 |     4 |    20 |     1 |
|  13 |     67 |      1 |     5 |    10 |     1 |
|  13 |     67 |      1 |     6 |    10 |     1 |
|  13 |     67 |      1 |     7 |    20 |     1 |
|  13 |     68 |      1 |     1 |    45 |     2 |
|  13 |     68 |      1 |     1 |    40 |     3 |
|  13 |     68 |      1 |     2 |    75 |     1 |
|  13 |     68 |      1 |     5 |    40 |     1 |
|  13 |     68 |      1 |     6 |    42 |     1 |
|  13 |     68 |      1 |     7 |    40 |     2 |
|  13 |     68 |      1 |     7 |    40 |     3 |
|  13 |     69 |      1 |     3 |    19 |     1 |
|  13 |     69 |      1 |     1 |    19 |     1 |
|  13 |     69 |      1 |     2 |    18 |     1 |
|  13 |     69 |      1 |     4 |    20 |     1 |
|  13 |     69 |      1 |     5 |    12 |     1 |
|  13 |     69 |      1 |     6 |    11 |     1 |
|  13 |     69 |      1 |     7 |    15 |     1 |
+-----+--------+--------+-------+-------+-------+

4) tbl_exam

     +-------+-----+-------+-----+------+
     | class | sid | paper | MaM | ExId |
     +-------+-----+-------+-----+------+
     |    13 |   3 |     1 |  25 |   64 |
     |    13 |   1 |     1 |  25 |   64 |
     |    13 |   2 |     1 |  25 |   64 |
     |    13 |   4 |     1 |  25 |   64 |
     |    13 |   5 |     1 |  13 |   64 |
     |    13 |   6 |     1 |  12 |   64 |
     |    13 |   7 |     1 |  25 |   64 |
     |    13 |   3 |     1 |  25 |   65 |
     |    13 |   1 |     1 |  25 |   65 |
     |    13 |   2 |     1 |  25 |   65 |
     |    13 |   4 |     1 |  25 |   65 |
     |    13 |   5 |     1 |  13 |   65 |
     |    13 |   6 |     1 |  12 |   65 |
     |    13 |   7 |     1 |  25 |   65 |
     |    13 |   3 |     2 |  50 |   66 |
     |    13 |   1 |     1 | 100 |   66 |
     |    13 |   2 |     1 | 100 |   66 |
     |    13 |   4 |     1 | 100 |   66 |
     |    13 |   5 |     1 |  50 |   66 |
     |    13 |   6 |     1 |  50 |   66 |
     |    13 |   7 |     1 | 100 |   66 |
     |    13 |   3 |     1 |  25 |   67 |
     |    13 |   1 |     1 |  25 |   67 |
     |    13 |   2 |     1 |  25 |   67 |
     |    13 |   4 |     1 |  25 |   67 |
     |    13 |   5 |     1 |  13 |   67 |
     |    13 |   6 |     1 |  12 |   67 |
     |    13 |   7 |     1 |  25 |   67 |
     |    13 |   3 |     2 |  50 |   68 |
     |    13 |   3 |     3 |  50 |   68 |
     |    13 |   1 |     2 |  50 |   68 |
     |    13 |   1 |     3 |  50 |   68 |
     |    13 |   2 |     1 | 100 |   68 |
     |    13 |   4 |     2 |  50 |   68 |
     |    13 |   4 |     3 |  50 |   68 |
     |    13 |   5 |     1 |  50 |   68 |
     |    13 |   6 |     1 |  50 |   68 |
     |    13 |   7 |     2 |  50 |   68 |
     |    13 |   7 |     3 |  50 |   68 |
     |    13 |   3 |     1 |  25 |   69 |
     |    13 |   1 |     1 |  25 |   69 |
     |    13 |   2 |     1 |  25 |   69 |
     |    13 |   4 |     1 |  25 |   69 |
     |    13 |   5 |     1 |  13 |   69 |
     |    13 |   6 |     1 |  12 |   69 |
     |    13 |   7 |     1 |  25 |   69 |
     |    13 |   3 |     3 |  50 |   66 |
     +-------+-----+-------+-----+------+ 

I have written a sql code to generate an output like this:

SELECT 
ts.subject as 'SUBJECT',
tme.exnm as 'EXAM',
tc.Marks as 'MARKS',
tc.paper as 'PAPER'
FROM
tbl_cmarks tc,
tbl_subjects ts,
tbl_masterexam tme
WHERE tc.rollno = 1 AND 
ts.id = tc.subId AND 
tme.exid = tc.Examid

It Produces an output Like this :

      +-------------------+-------------------+-------+-------+
      |      SUBJECT      |       EXAM        | MARKS | PAPER |
      +-------------------+-------------------+-------+-------+
      | ENGLISH           | Unit Test-1       |    21 |     1 |
      | తెలుగు            | Unit Test-1       |    18 |     1 |
       | हिन्दी            | Unit Test-1       |    20 |     1 |
      | MATHEMATICS       | Unit Test-1       |    23 |     1 |
      | PHYSICAL SCIENCES | Unit Test-1       |    11 |     1 |
       | BIOLOGY           | Unit Test-1       |    11 |     1 |
       | SOCIAL STUDIES    | Unit Test-1       |    22 |     1 |
       | ENGLISH           | Unit Test-2       |    20 |     1 |
       | తెలుగు            | Unit Test-2       |    20 |     1 |
       | हिन्दी            | Unit Test-2       |    19 |     1 |
       | MATHEMATICS       | Unit Test-2       |    22 |     1 |
       | PHYSICAL SCIENCES | Unit Test-2       |    10 |     1 |
       | BIOLOGY           | Unit Test-2       |    10 |     1 |
       | SOCIAL STUDIES    | Unit Test-2       |    20 |     1 |
       | SOCIAL STUDIES    | Unit Test-2       |    20 |     1 |
       | తెలుగు            | Quarterly Exams   |    75 |     1 |
       | हिन्दी            | Quarterly Exams   |    89 |     1 |
       | MATHEMATICS       | Quarterly Exams   |    80 |     1 |
       | PHYSICAL SCIENCES | Quarterly Exams   |    45 |     1 |
       | BIOLOGY           | Quarterly Exams   |    42 |     1 |
       | SOCIAL STUDIES    | Quarterly Exams   |    89 |     1 |
       | ENGLISH           | Half yearly Exams |    41 |     2 |
       | ENGLISH           | Half yearly Exams |    35 |     3 |
       | MATHEMATICS       | Half yearly Exams |    40 |     2 |
       | MATHEMATICS       | Half yearly Exams |    45 |     3 |
       | ENGLISH           | Quarterly Exams   |    40 |     2 |
       | ENGLISH           | Quarterly Exams   |    41 |     3 |
       | ENGLISH           | Unit Test-3       |    16 |     1 |
       | తెలుగు            | Unit Test-3       |    19 |     1 |
       | MATHEMATICS       | Unit Test-3       |    20 |     1 |
       | PHYSICAL SCIENCES | Unit Test-3       |    10 |     1 |
       | BIOLOGY           | Unit Test-3       |    10 |     1 |
       | SOCIAL STUDIES    | Unit Test-3       |    20 |     1 |
       | తెలుగు            | Half yearly Exams |    45 |     2 |
       | తెలుగు            | Half yearly Exams |    40 |     3 |
       | हिन्दी            | Half yearly Exams |    75 |     1 |
       | PHYSICAL SCIENCES | Half yearly Exams |    40 |     1 |
       | BIOLOGY           | Half yearly Exams |    42 |     1 |
       | SOCIAL STUDIES    | Half yearly Exams |    40 |     2 |
       | SOCIAL STUDIES    | Half yearly Exams |    40 |     3 |
       | ENGLISH           | Unit Test-4       |    19 |     1 |
        | తెలుగు            | Unit Test-4       |    19 |     1 |
       | हिन्दी            | Unit Test-4       |    18 |     1 |
      | MATHEMATICS       | Unit Test-4       |    20 |     1 |
      | PHYSICAL SCIENCES | Unit Test-4       |    12 |     1 |
       | BIOLOGY           | Unit Test-4       |    11 |     1 |
       | SOCIAL STUDIES    | Unit Test-4       |    15 |     1 |
       +-------------------+-------------------+-------+-------+

But I want to Display the result as :

      +-------------------+---------+---------+---------+---------+---------+---------+
      |                   |  U.T-1  |  U.T-2  |  Quart  |  U.T-3  |  Half   | U.T-4   |
      +-------------------+---------+---------+---------+---------+---------+---------+
      | SUBJECT           | M Max G | M Max G | M Max G | M Max G | M Max G | M Max G |
      | ENGLISH         | 21 25 A | 21 25 A | 40,45 50,50 A | 21 25 A | 45,40 50,50 A | 21 25 A |
      |  తెలుగు           | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
      |  हिन्दी           | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
      | MATHEMATICS       | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
      | PHYSICAL SCIENCES | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
      | BIOLOGY           | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
      | SOCIAL STUDIES    | 21 25 A | 21 25 A | 85 100 A | 21 25 A | 45,40 50,50 A | 21 25 A |
      +-------------------+---------+---------+---------+---------+---------+---------+

Please anyone tell me where should i change my query to reproduce the desired result

I have tried GROUP_CONCAT(exnm) But i am not able to get the desired result. Please tell me / give me an idea to write the code to display like this.

Thank you.

To Display the Grade i have created a Function like this

      DELIMITER $$
      CREATE FUNCTION `ShowGrade`(val FLOAT(10,2)) RETURNS text CHARSET latin1
      BEGIN
      DEClARE p FLOAT(10,2);    
      DEClARE g TEXT;
      set p=val;
  IF p>=91 and p<=100 THEN 
    set g= "A+";
  END IF;
  IF p>=71 and p<=90 THEN 
        set g= "A";
    END IF;
  IF p>=51 and p<=70 THEN 
    set g= "B+";
    END IF;
  IF p>=41 and p<=50 THEN 
    set g= "B";
    END IF;
  IF p>=0 and p<=40 THEN 
    set g= "C";
    END IF;
      return g;
     END
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You can do it like this

SET @sql = NULL;

SELECT GROUP_CONCAT(
         CONCAT('MAX(CASE WHEN examid = ''', exid,
         ''' THEN marks END) `', exnm, '`'))
  INTO @sql
  FROM tbl_masterexam
 WHERE cid = 13;

SET @sql = CONCAT('SELECT s.subject, ', @sql, 
                  '  FROM tbl_Cmarks m JOIN Tbl_subjects s 
                       ON m.subid = s.id 
                    WHERE m.rollno = 1
                      AND m.cid = 13
                    GROUP BY m.subid, s.subject');

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

Output:

|           SUBJECT | UNIT TEST-1 | UNIT TEST-2 | QUARTERLY EXAMS | UNIT TEST-3 | HALF YEARLY EXAMS | UNIT TEST-4 | PRE - PUBLIC EXAMS |
|-------------------|-------------|-------------|-----------------|-------------|-------------------|-------------|--------------------|
|             తెలుగు |          18 |          20 |              75 |          19 |                45 |          19 |             (null) |
|               हिन्दी |          20 |          19 |              89 |      (null) |                75 |          18 |             (null) |
|           ENGLISH |          21 |          20 |              41 |          16 |                41 |          19 |             (null) |
|       MATHEMATICS |          23 |          22 |              80 |          20 |                45 |          20 |             (null) |
| PHYSICAL SCIENCES |          11 |          10 |              45 |          10 |                40 |          12 |             (null) |
|           BIOLOGY |          11 |          10 |              42 |          10 |                42 |          11 |             (null) |
|    SOCIAL STUDIES |          22 |          20 |              89 |          20 |                40 |          15 |             (null) |

Here is SQLFiddle demo

share|improve this answer
    
@Narendar_CH IS it what you were looking for? –  peterm Aug 29 '13 at 18:15
    
Yes sir this is the output i am trying to achieve. Thank you for your help. –  Narendar_CH Aug 30 '13 at 4:59
    
I want to show comma separated Marks for those Exams Having Paper Column 2,3. where should i modify this script to do so –  Narendar_CH Aug 30 '13 at 5:01
    
sir the result structure is changed, Is this possible to get the result like i mentioned /edited ? –  Narendar_CH Aug 30 '13 at 11:41
    
Could you help me, please. –  Narendar_CH Aug 30 '13 at 14:19
show 8 more comments

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.