A complex SQL query requirement for a table containing data in unusual way

Question

I have a bit complex situation for extracting information from a table. I am not sure what is the best way to do it.

I have two tables : student student_information

student

id          name
--          --------
1           shredder
2           Queen

student_infromation

student_id  property    value
----------  --------    -----
1           dept        CS
1           address     DE
1           class       X
2           dept        MS
2           address     DE
2           class       IX

Running:

select * from student
join student_information si on student.id = si.student_id

Gives me output like this :

id          name        student_id  property    value
--          --------    ----------  --------    -----
1           shredder        1       dept        CS
1           shredder        1       address     DE
1           shredder        1       class       X
2           queen           2       dept        MS
2           queen           2       address     DE
2           queen           2       class       IX



select * from student
join student_information si on student.id = si.student_id
where property in ('dept','class')

I need information just for Dept and Class for each student,but currently the property class and department is listed as row. I want to make column out of it.

Can we construct the table like this?

id          name        dept        class
--          --------    ----------  ------
1           shredder    cS          X       
1           queen       MS          IX  

Answer 1

You may join the two tables and then aggregate by student, turning out the properties you want using pivoting logic:

SELECT
    s.id,
    s.name,
    MAX(CASE WHEN si.property = 'dept'  THEN si.value END) AS dept,
    MAX(CASE WHEN si.property = 'class' THEN si.value END) AS class
FROM student s
LEFT JOIN student_information si
    ON s.id = si.student_id
GROUP BY
    s.id,
    s.name;

Demo