Here is the data I have in my database. I am trying to make each proptype a header and then comma delimited the propdetails that match.

Here is what I am looking for in the end result.

Here is my current query:

select propid a ,
case when proptype = 'house' then (SELECT group_concat(propdetails) FROM propdatabase where a=propid and proptype ='house' ) end as house,
case when proptype = 'apartment' then (SELECT group_concat(propdetails) FROM propdatabase where a=propid and proptype ='apartment') end as apartment  ,
case when proptype = 'pool' then (SELECT group_concat(propdetails) FROM propdatabase where a=propid and proptype ='pool' ) end as pool,  
case when proptype = 'acreage' then (SELECT group_concat(propdetails) FROM propdatabase where a=propid and proptype ='acreage' ) end as acreage 
from propdatabase group by proptype

This is my result. As you can see, the propid is being listed once per each proptype. The comma-delimiting is working which is great. I just need to figure out how to put all the information per propid onto one line.

CodePudding user response：

This is a type of pivot-table query.

I have not tested the following, but it shows the technique. Group by the propid column if you want to reduce the result to one row per distinct value in that column.

Use a conditional expression like CASE to return the propdetails value only if another condition is met, or else NULL. The GROUP_CONCAT() ignores NULLs.

SELECT propid AS a,
  GROUP_CONCAT(CASE proptype WHEN 'house' THEN propdetails END) AS `house`,
  GROUP_CONCAT(CASE proptype WHEN 'acreage' THEN propdetails END) AS `acreage`,
  GROUP_CONCAT(CASE proptype WHEN 'pool' THEN propdetails END) AS `pool`,
  GROUP_CONCAT(CASE proptype WHEN 'apartment' THEN propdetails END) AS `apartment`
FROM propdatabase
GROUP BY propid;