mySQL grouping data together into header columns

user_2240602 0
Viewed 27

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.

propid proptype propdetails
id1234 house 2bedroom
id1234 house 2bathroom
id1234 acreage noland
id1234 pool nopool
id999 apartment 2bathroom
id999 apartment 2bedroom

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

propid house acreage pool apartment
id1234 2bedroom,2bathroom noland nopool
id999 2bathroom,2bedroom

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.

propid house acreage pool apartment
id1234 2bedroom,2bathroom
id1234 noland
id1234 nopool
id999 2bathroom,2bedroom
pivot-table mysql-workbench group-concat mysql
1 Answers

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;
Bill_Karwin_208600
Related