CODEWITHSUNDEEP
mysqlsqlmariadbpivot
MMEL
MMEL

Reputation: 358

Simple MySQL Pivot using 2 tables in INPUT

I know that a lot of PIVOT questions have been asked in SOF but they all use complex examples with SUM and Groups and I really do not have such need, do not understand so well and so I would appreciate help in this simple example (no SUM nor aggregation needed in this one):

A list of students(student_id) take classes in which they obtain grades (class_value) 1 table, lets call it "class_names" has the list of class names and their IDs

table "class_names"

1 table (lets call it "student_grades") has the year_id, the student and their grades in each class

table "student_grades"

The result we expect to produce is a View(or table/preferably just a View) with pivoted columns as shown below:

enter image description here

as you can see in the result we lose the class_id and replace it with the class_name (I imagine using a join) and then also important to note, a student does not necessarily take all classes and in which case the value for the grade should be just NULL as it is shown above.

IMPORTANT consideration: We do NOT know the class names in advance and there could be HUNDREDS of them so adding them manually to the SQL request may not be the right approach or even feasible since there may be new names added with time in that class_names table

We wish to get an example on how to do this in MySQL (or MariaDb to be precise) BUT not TSQL Thanks.

Upvotes: 0

Views: 283

Answers (2)

MMEL
MMEL

Reputation: 358

IF you do not know in advance the list of classes in the "class_names" table, then one way to do it is to use dynamic SQL and build the SQL request first with the list of classes to be included. Then add additional fields such as the year and student_id columns. Then prepare and execute that request.

-- 1. Create an expression that builds the columns

set @sql = (
    select group_concat(distinct 
        concat(
            "sum(case when `class_name`='", c.class_name, "' then `class_value` end) as `", `class_name`, "`"
        )
    )
    from class_names c
    inner join student_grades s on s.class_id = c.class_id
);

-- 2. Complete the SQL instruction

set @sql = concat("select s.year_id, s.student_id, ", @sql, 
                  "from class_names c inner join student_grades s 
                   on s.class_id = c.class_id 
                   group by s.year_id, s.student_id"
                 );

-- 3. Create a prepared statement

prepare stmt from @sql;

-- 4. Execute the prepared statement

execute stmt;

Please note that if your table "class_names" has a real big number of classes, then you might have to extend the group_concat "max_len" limit by doing the following:

SET group_concat_max_len=15000;

IF you do not, you might get a weird syntax error because your @sql string was actually truncated due to the fact that the group_concat had reached its limit...

Upvotes: 0

GMB
GMB

Reputation: 222482

You can join and then do conditional aggregation:

select c.year_id, c.student_id,
    max(case when c.class_name = 'class1' then s.class_value end) class1,
    max(case when c.class_name = 'class2' then s.class_value end) class2,
    max(case when c.class_name = 'class3' then s.class_value end) class3,
    ... 
from class_names c
inner join student_grades s on s.class_id = c.class_id
group by c.year_id, c.student_id

Note that this requires that you know in advance the list of class names. Otherwise, you would need to use dynamic SQL (that is, dyncamically build the query string, then execute it), which is much more complicated.

Upvotes: 1

Related Questions