Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / NewStats: 3,194,378 members, 7,954,531 topics. Date: Friday, 20 September 2024 at 09:30 PM |
Nairaland Forum / Science/Technology / Programming / Mysql Help:please I Need Help Transposing Row Into Column (958 Views)
Mysql Group By Displaying One Row Instead Of Two / Free Online Training For Html/css, Javascript, Jquery, Mysql, Php, Java, Android / Learn PHP & MYSQL The Fast Way. (2) (3) (4)
Mysql Help:please I Need Help Transposing Row Into Column by efelico: 6:48am On Mar 20, 2020 |
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 |
Re: Mysql Help:please I Need Help Transposing Row Into Column by vheckthor1: 11:55am On Mar 20, 2020 |
Your questions are hard to comprehend please attach a picture for more clarity 1 Like |
Re: Mysql Help:please I Need Help Transposing Row Into Column by efelico: 2:12pm On Mar 20, 2020 |
This is the subject table
|
Re: Mysql Help:please I Need Help Transposing Row Into Column by efelico: 2:14pm On Mar 20, 2020 |
this is the scores table in it un-transpose state
|
Re: Mysql Help:please I Need Help Transposing Row Into Column by efelico: 2:16pm On Mar 20, 2020 |
and this is the expected outcome. this is what i want to produce. Thank you
|
Re: Mysql Help:please I Need Help Transposing Row Into Column by Nobody: 4:09pm On Mar 20, 2020 |
efelico:Use joints. SELECT * FROM scores LEFT JOIN subjects ON scores.id =subjects.id 1 Like |
Re: Mysql Help:please I Need Help Transposing Row Into Column by Nobody: 4:27pm On Mar 20, 2020 |
efelico: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. |
Re: Mysql Help:please I Need Help Transposing Row Into Column by efelico: 6:07pm On Mar 20, 2020 |
zerateck: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 |
Re: Mysql Help:please I Need Help Transposing Row Into Column by efelico: 6:35pm On Mar 20, 2020 |
zerateck: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 |
(1) (Reply)
Help Python Syntax Error! / How On-demand App Development Is Transforming Businesses / Smart Web Programmers Needed For Long Term Partnership On A Project
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 14 |