CODEWITHSUNDEEP
mysql
C Java
C Java

Reputation: 446

Display MySQL columns horizontally

I have a simple MySQL query, in a table in phpMyAdmin. I want to display the results horizontally, not vertically.

SELECT `FieldName`,`FieldValue` FROM `table` 

Fieldname: name, surname, age etc
Fieldvalue: john, doe, 25 etc

I show in the image the way it is displayed, and how I want it to be displayed. The way it is displayed now:

1) Displayed 2- how to display it enter image description here

Edit: pivot only works for a few records. Not thousands. Cannot specify each value one by one.

Upvotes: 1

Views: 1893

Answers (1)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT GROUP_CONCAT(Name SEPARATOR '') AS Name,
       GROUP_CONCAT(Surname SEPARATOR '') AS Surname,
       GROUP_CONCAT(Age SEPARATOR '') AS Age
FROM
(
    SELECT 
    CASE WHEN FieldName = 'Name' THEN @rownum:=@rownum+1 
         ELSE @rownum
    END AS id,
    IF( FieldName = 'Name', FieldValue,'')  AS Name,  
    IF( FieldName = 'Surname', FieldValue,'') AS Surname, 
    IF( FieldName = 'Age', FieldValue,'' ) AS Age 
    FROM 
    Table1 ,(SELECT @rownum:=0) r
)AS t 
GROUP BY id

Demo for the Question:

http://sqlfiddle.com/#!9/61b00c/8

Upvotes: 1

Related Questions