select
tab1.created_at ,
-- SUBSTRING_INDEX(merged_col3, ',', 1) AS col3_1,
max(CASE
WHEN tab1.participating_nature_id LIKE '%1%' THEN tab1.name
ELSE NULL
END) AS col3_value,
max(CASE
WHEN tab1.participating_nature_id LIKE '%2%' THEN tab1.name
-- SUBSTRING_INDEX(tab1.participating_nature_id, ',', 1)
ELSE NULL
END) AS col4_value,
max(CASE
WHEN tab1.participating_nature_id LIKE '%3%' THEN tab1.name
-- SUBSTRING_INDEX(tab1.participating_nature_id, ',', 1)
ELSE NULL
END) AS col5_value,
max(CASE
WHEN tab1.participating_nature_id LIKE '%4%' THEN tab1.name
-- SUBSTRING_INDEX(tab1.participating_nature_id, ',', 1)
ELSE NULL
END) AS col6_value,
max(CASE
WHEN tab1.participating_nature_id LIKE '%5%' THEN tab1.name
-- SUBSTRING_INDEX(tab1.participating_nature_id, ',', 1)
ELSE NULL
END) AS col7_value,
max(CASE
WHEN tab1.post_id LIKE '%2%' THEN tab1.name1
-- SUBSTRING_INDEX(tab1.participating_nature_id, ',', 1)
ELSE NULL
END) AS col8_value,
max(CASE
WHEN tab1.post_id LIKE '%1%' THEN tab1.name1
-- SUBSTRING_INDEX(tab1.participating_nature_id, ',', 1)
ELSE NULL
END) AS col9_value
from ( SELECT
a.created_at, b.id, b.participating_nature_id, c.name ,d.post_id, f.name as name1
FROM
project_information a
left JOIN project_company as b ON a.id = b.project_id
LEFT JOIN company_information c ON c.id = b.company_id
LEFT JOIN project_member d on d.project_id = a.id
LEFT JOIN service_users f on f.id=d.member_id
) as tab1
GROUP BY tab1.created_at