Transpose of column to row in php

Question

I am facing a problem in transposing column to rows,

Here is the sample data pattern having number of column:

gender | Maths | English | science
----------------------------------
Male   | 20    | 89      | 59 
Female | 70    | 56      | 79

I would like to transpose it to below format

Subject | Male | Female
------------------------
Maths   | 20    | 70     
English | 89    | 56      
Science | 59    | 79

Your question sounds similar to https://stackoverflow.com/questions/30087158/how-can-i-rotate-a-2d-array-in-php-by-90-degrees is that what you're trying to do? — compiledweird, Dec 29 '20 at 06:58
Consider normalising your schema (e,g. student(student_id, gender), subject (subject_id, subject_name), grade(student_id,subject_id,grade) — Strawberry, Dec 29 '20 at 08:48

score 0 · Answer 1 · answered Dec 29 '20 at 04:59

We can try a union approach combined with a pivot query here:

SELECT
    Subject,
    MAX(CASE WHEN gender = 'Male'   THEN score END) AS Male,
    MAX(CASE WHEN gender = 'Female' THEN score END) AS Female
FROM
(
    SELECT 'Maths' AS Subject, gender, Maths AS score FROM yourTable
    UNION ALL
    SELECT 'English', gender, English FROM yourTable
    UNION ALL
    SELECT 'science', gender, science FROM yourTable
) t
GROUP BY
    Subject;

Transpose of column to row in php

1 Answers1

Demo