How to do Unpivot of columns to rows in MySQL 5.7

Question

I have sample data which I need to convert from columns to rows.

Input Data :

NAME   CLOTH SHOES  PHONE
MOHAN  YES   YES    YES
MAHESH YES   NO     NO

Output :

CATEGORY   MOHAN  MAHESH 
CLOTH      YES   YES
SHOES      YES   NO
PHONE      YES   NO

I have tried Case Condition but not able to move forward. Can anyone please suggest on this.

Note : For 5.7 and below cross join is not working

' Cross Join is not working for MYSQL 5.7 ' - Not so cross join has been in mysql forever - there must be some other issue in your unpublished code.. — P.Salmon, Mar 09 '23 at 13:12

score 0 · Answer 1 · answered Mar 09 '23 at 13:15

I have imported your data to SQLFIDDLE.

The following query shall give you the expected output :

SELECT 'CLOTH' AS category,
       MAX(CASE WHEN name = 'Mohan' THEN cloth END) AS mohan,
       MAX(CASE WHEN name = 'Mahesh' THEN cloth END) AS mahesh
FROM your_table
UNION ALL
SELECT 'SHOES' AS category,
       MAX(CASE WHEN name = 'Mohan' THEN shoes END) AS mohan,
       MAX(CASE WHEN name = 'Mahesh' THEN shoes END) AS mahesh
FROM your_table
UNION ALL
SELECT 'PHONE' AS category,
       MAX(CASE WHEN name = 'Mohan' THEN phone END) AS mohan,
       MAX(CASE WHEN name = 'Mahesh' THEN phone END) AS mahesh
FROM your_table;

It gives me output as :

category    mohan   mahesh
CLOTH       YES     YES
SHOES       YES     NO
PHONE       YES     No

How to do Unpivot of columns to rows in MySQL 5.7

1 Answers1