Search code examples
sqlmysqlpivot

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.

Solution 
  • SELECT Name, GROUP_CONCAT(Property, ' = ', value SEPARATOR ', ') Attributes
FROM test
GROUP BY Name;

    fiddle