Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL dynamic pivoting or something else

I have data in following format in an SQL table but need to present it differently.

Name Property value
Mark Gender Male
Mark Age 45
Mark Weight 167
Chris Gender Male
Chris height 188

I’d very much like to present/select it like this, in an SQL query ideally:

Name Attributes
Mark Gender = Male, Age=45, Weight=167
Chris Gender = Male, Height=188

I understand that dynamic pivoting may be an option but this is new to me. Is this correct and if so could anyone help create the query?
Any other suggestions would be welcome.

Many thanks.

I’ve tried UNIONS etc and this is the limit of my SQL knowledge.
I also have the option to use an ETL tool which may get the results but I’d like to just have an SQL query to run.

Tags:

2

Answers


  1. 0 
    SELECT Name, GROUP_CONCAT(Property, ' = ', value SEPARATOR ', ') Attributes
FROM test
GROUP BY Name;

    fiddle

    Login or Signup to reply.
  2. 0

    you can also try this query by using a join

    SELECT t1.Name, GROUP_CONCAT(t1.Property, ' = ', t1.value SEPARATOR ', ') AS Attributes FROM test t1 JOIN (SELECT DISTINCT Name FROM test) t2 ON t1.Name = t2.Name GROUP BY t1.Name ORDER BY t1.Name DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search