1

I have a table like the one below:

+-------+-----------+-----------+-----+--------+-------------+
| grade | firstname | lastname  | age | gender | student_num |
+-------+-----------+-----------+-----+--------+-------------+
|     2 | Stephen   | Harper    |  12 | male   |           1 |
|     1 | Jennifer  | Solomon   |  10 | female |           1 |
|     4 | James     | Barney    |   9 | female |           1 |
|     3 | Collins   | Balmer    |   8 | female |           1 |
|     4 | Kehinde   | Adefemi   |  12 | male   |           2 |
|     2 | Benjamin  | Salem     |  14 | female |           2 |
|     3 | Praise    | Olawale   |   9 | male   |           2 |
|     1 | Janet     | Pelumi    |   7 | male   |           2 |
|     3 | Ire       | Adora     |  11 | female |           3 |
|     2 | Manny     | Grace     |  13 | male   |           3 |
|     2 | Esther    | Benson    |   7 | female |           4 |
|     3 | Stan      | Collimore |   6 | female |           4 |

Please help me with the SQL that will generate the resulting table below from the one above.

grade|s1_fname  |s1_age|s1_gender|s2_fname |s2_age|s2_gender|
  1  | Jennifer | 10   |female   | Janet   | 7    |  male   |
  2  | Stephen  | 12   |male     | Benjamin| 14   |  female |
  3  | Collins  | 8    |female   | Praise  | 9    |  male   |
  4  | Kehinde  | 12   |male     | Myedan  | 8    |  male   |

where s1 means student1. The 1 should be gotten from from student_num column

where s2 means student2. The 2 should be gotten from from student_num column ...

My database server is MySQL.

I Have tried the code below

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
          'SUM(CASE WHEN `grade` = ',
          `grade`,
          ' THEN firstname ELSE 0 END) AS `s',
          `student_num`, '_fname`'
        )
    ) INTO @sql
FROM students;
SET @sql = CONCAT('SELECT grade, ', @sql, ' FROM students GROUP BY grade');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  • is this an assignment question? sounds like someone cheating :p – Masoud May 19 '15 at 7:52
  • @Masoud it isn't please. can you help me? – user3142200 May 19 '15 at 7:58
  • I will only if you tell me what is the query trying to achieve? I mean the relation between the result and the raw table. – Masoud May 19 '15 at 8:08
  • @masoud, i want to GROUP BY grade, and for each grade all students for that grade should be on the GROUPED BY grade row. The final destination is a CSV file, and its a client request. Also i have posted the code i have tried above.Thanks – user3142200 May 19 '15 at 8:41
1

You could do something along the following lines:

select 
    s1.grade, 
    s1.firstname as s1_fname, 
    s2.firstname as s2_fname, 
    s3.firstname as s3_fname,
    s4.firstname as s4_fname
from #grades as s1
-- Self join the table for second student
left outer join grades as s2
    on s1.grade = s2.grade 
    and s2.student_num = 2
-- Self join the table for third student
left outer join grades as s3
    on s1.grade = s3.grade 
    and s3.student_num = 3
-- Self join the table for forth student
left outer join grades as s4
    on s1.grade = s4.grade 
    and s4.student_num = 4
where s1.student_num = 1
order by s1.grade;

This joins the table to itself once for each set of columns (s1, s2, etc.). You could write some dynamic SQL to generate this based on the maximum student_num or just run select max(student_num) and make sure you have enough columns to catch all the values. (If you don't you'll need to add another join like the others).

Edit: This assumes that the grade and student_num columns combine to create a unique key. If you have duplicates (e.g. two students with grade 1, student_num 1), then you'll get duplicate rows in your results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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