You essentially want to pivot the
meta_value column. Some SQL products have dedicated syntax for this operation. MySQL does not have it, but there is a universal method that works in most products, including MySQL: conditional aggregation.
MAX(CASE meta_key WHEN 'first_name' THEN meta_value END) AS first_name,
MAX(CASE meta_key WHEN 'last_name' THEN meta_value END) AS last_name,
MAX(CASE meta_key WHEN 'street_add' THEN meta_value END) AS street_add,
MAX(CASE meta_key WHEN 'city' THEN meta_value END) AS city,
MAX(CASE meta_key WHEN 'state' THEN meta_value END) AS state
meta_key IN ('first_name','last_name','street_add','city','state')
Fundamentally, this is the same approach as the one suggested by SQLHound, except, in my opinion, there is no need to create a view specifically to solve this problem. (Although it may make sense to create a view for a particular set of pivoted columns if that information is requested often or used in many other queries – makes those queries simpler and more maintainable.) Also, as you can see, pivoting can be done as a single step operation – no need to derive a set of CASEs first and only then aggregate them.