i have a score table similiar to this
regid---------------test1----------------------exam-----------------subject
3m----------------5----------------------------8---------------------12
4m--------------8-------------------------------14------------------13
3m----------------9-----------------------------24------------------13
and a score table similar to this
id------------------subject_name
12-------------------maths
13------------------english
I want to transpose the row and column using dynamic pivot table in mysql. i can transpose to get something like
regid________maths______________________english
3m----------- 5---------------------------9
4m-----------null------------------------8
it simply means i can transpose to get only column for test but i want column for exam in the transpose table so that the result will be like
regid________maths.test______________________maths.exam____________english.test_________________english.exam
3m----------- 5---------------------------------------8----------------------9-----------------------------24
4m-----------null-----------------------------------null-------------------------8--------------------------14
I have tried SO no answer yet. I have tried writing something like
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(case when subject = ''',
subject,
''' then test end)AS `',
(select subject_name from subject where id=subject), '`',

'SUM(case when subject = ''',
subject,
''' then exam end)AS `',
(select subject_name from subject where id=subject), '`'
)
) INTO @sql
FROM
scores;
SET @sql = CONCAT('SELECT reg_id, ', @sql, '
FROM scores where term="First term"
GROUP BY reg_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Thanks for any assistance

Your questions are hard to comprehend please attach a picture for more clarity

This is the subject table

this is the scores table in it un-transpose state

and this is the expected outcome. this is what i want to produce. Thank you

efelico:
and this is the expected outcome. this is what i want to produce. Thank you

Use joints.

SELECT * FROM scores
LEFT JOIN subjects ON scores.id =subjects.id

efelico:
and this is the expected outcome. this is what i want to produce. Thank you

Please take note this your database table design is not efficient, the database design is bad.

What if you have plenty subjects it will keep expanding the columns.
The number of columns should be kept at minimum. Ideally, columns shouldn't exceed 10.

I have written exhaustively on database normalization on this forum you can search and read.

zerateck:

Please take note this your database table design is not efficient, the database design is bad.

What if you have plenty subjects it will keep expanding the columns.
The number of columns should be kept at minimum. Ideally, columns shouldn't exceed 10.

I have written exhaustively on database normalization on this forum you can search and read.

I really appreciate your time. That is not my main table(The score table is my main design). I am transposing the score table (The main table) to achieve that purpose. I am trying to generate a school broad sheet from the score table. I just want to try this approach as it does not need much processing from the application layer and to learn another way of achieving tasks related to something like this. I really appreciate cause I never consider the number of columns however the page will be rarely visited once the broadsheet is download. I will check your post as you did mentioned. I really appreciate thank you Sir

zerateck:

Use joints.

SELECT * FROM scores
LEFT JOIN subjects ON scores.id =subjects.id

I have a working code on a simple left joint just that it requires merging and looping over arrays but case statement simply does everything from the database level