将mysql查询结果格式化为所需格式

Question

I am trying to get my result from MYSQL query in the below format

Date: '2016-01-01', Resource_1: 100, Resource_2: 90, Resource_3:30, Resource_4: 20
Date: '2016-01-02', Resource_1: 0, Resource_2: 20, Resource_3:100, Resource_4: 40
Date: '2016-01-03', Resource_1: 70, Resource_2: 40, Resource_3:0, Resource_4: 50
Date: '2016-01-04', Resource_1: 10, Resource_2: 0, Resource_3:30, Resource_4: 0

My MYSQL result is coming in the below format:

2016-01-01|Resource_1|100
2016-01-01|Resource_2|90
2016-01-01|Resource_3|30
2016-01-01|Resource_4|20
2016-01-02|Resource_1|0
2016-01-02|Resource_2|20
2016-01-02|Resource_3|100
2016-01-02|Resource_4|40
2016-01-03|Resource_1|70
2016-01-03|Resource_2|40   
2016-01-03|Resource_3|0
2016-01-03|Resource_4|50
2016-01-04|Resource_1|10
2016-01-04|Resource_2|0
2016-01-04|Resource_3|30
2016-01-04|Resource_4|0

How do I get it into the desired format in MYSQL.

I would like to mention that my table structure is as follows:

DATE|RESOURCE_NAME|MARKS

I used the below query to get the previous query result

SELECT cd.cd_claim_dt AS cdt,res.res_Name AS NAME,
SUM(CASE WHEN MONTH(cd.cd_claim_dt) = 1 AND YEAR(cd.cd_claim_dt) = 2016 THEN    
ct.ct_duration ELSE 0 END) claimed_hrs
FROM 
tbl_claim_data cd, tbl_claim_time ct, tbl_resourceinfo res
WHERE 
ct.cd_slno = cd.cd_slno AND MONTH(cd.cd_claim_dt) = 1 AND   
YEAR(cd.cd_claim_dt) = 2016 AND cd.app_SlNo = 35 AND 
cd.res_slno IN(17,22,23,24) AND res.res_SlNo = cd.res_slno
GROUP BY 
cd.res_slno, cd.cd_claim_dt, cd.cd_status
ORDER BY 
cd.cd_claim_dt

Answer 1

try this query

select group_concat(concat('date:',DATE,':',RESOURCE_NAME,':',MARKS)
 separator ' , ' )from mytable group by DATE order by DATE desc;

You can also use concat_ws() instandof concat() function