1

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.

TomPlum
  • 135
  • 1
  • 14
C Java
  • 446
  • 2
  • 16
  • 2
    Search this site for *MySQL Pivot* and see if you can find something that works for your needs. Once you've done so, and you've made an effort to do something and run into difficulties, you can come back and ask. Good luck. – Ken White Mar 21 '18 at 12:56
  • 2
    Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Raymond Nijland Mar 21 '18 at 13:00
  • Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Raymond Nijland Mar 21 '18 at 13:01
  • use pivot see [pivot-table-using-mysql](http://webdevzoom.com/pivot-table-using-mysql/) – SF..MJ Mar 21 '18 at 13:02
  • Edit: pivot only works for a few records. Not thousands. Cannot specify each value one by one. – C Java Mar 21 '18 at 13:25
  • But... Does FieldName contain 'Name' more than once? – Francesco B. Mar 21 '18 at 13:27

1 Answers1

1
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

Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27