how to use if statement in Group_Concat in Mysql

Question

After my head bangs to the wall, I am trying to have a pivot data with this sample table content.

id--------------lastname-----total----rd               
0511-02-0318    Salvador    8.99    2015-09-08
0214-02-0616    Alfaro  0       2015-09-08
0214-02-0616    Alfaro  7.95    2015-09-07
0214-02-0616    Alfaro  8.69    2015-09-04
0214-02-0616    Alfaro  6.81    2015-09-01
0214-02-0616    Alfaro  8.86    2015-08-29
0214-02-0616    Alfaro  6.16    2015-08-28
0214-02-0616    Alfaro  8.66    2015-08-27
0214-02-0616    Alfaro  8.03    2015-08-26
0214-02-0616    Alfaro  8.68    2015-08-25
0214-02-0616    Alfaro  8.75    2015-08-24
0511-02-0318    Salvador 9.17   2015-09-09
0511-02-0318    Salvador 9.28   2015-09-10
0415-02-0960    Martin   0      2015-09-18
0415-02-0960    Martin  10.39   2015-09-17
0415-02-0960    Martin  8.44    2015-09-16
0415-02-0960    Martin  10.9    2015-09-15
0415-02-0960    Martin  10.91   2015-09-14
0415-02-0960    Martin  9.91    2015-09-11
0415-02-0960    Martin  10.31   2015-09-10
0415-02-0960    Martin  11.16   2015-09-09
0415-02-0960    Martin  12.04   2015-09-08
0415-02-0960    Martin  9.42    2015-09-07
0415-02-0960    Martin  10.43   2015-09-04
0415-02-0960    Martin  10.39   2015-09-03

This is my working attemp Query but i think i missed up something:

SET @sql = NULL;
set @sdate = '2015-09-01';
set @edate = '2015-09-06';

SELECT GROUP_CONCAT(

distinct concat('total=',list_of_dates)

/*distinct concat('if(p.rd=''',list_of_dates,''',p.total',0,')')*/

) INTO @sql from
(SELECT ADDDATE(date(@sdate), INTERVAL @i:=@i+1 DAY) AS list_of_dates
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF(date(@edate),date(@sdate))) as a;





set @sql = concat('select id,   lastname,   total,   rd,',@sql,'  from
(select ''0511-02-0318'' as id, ''Salvador'' as lastname,   8.99 as total,  ''2015-09-08'' as rd union all
select  ''0214-02-0616'',   ''Alfaro'', 0   ,''2015-09-08''  union all
select  ''0214-02-0616'',   ''Alfaro'', 7.95,   ''2015-09-07''  union all
select  ''0214-02-0616'',   ''Alfaro'', 8.69,   ''2015-09-04''  union all
select  ''0214-02-0616'',   ''Alfaro'', 6.81,   ''2015-09-01''  union all
select  ''0214-02-0616'',   ''Alfaro'', 8.86,   ''2015-08-29''  union all
select  ''0214-02-0616'',   ''Alfaro'', 6.16,   ''2015-08-28''  union all
select  ''0214-02-0616'',   ''Alfaro'', 8.66,   ''2015-08-27''  union all
select  ''0214-02-0616'',   ''Alfaro'', 8.03,   ''2015-08-26''  union all
select  ''0214-02-0616'',   ''Alfaro'', 8.68,   ''2015-08-25''  union all
select  ''0214-02-0616'',   ''Alfaro'', 8.75,   ''2015-08-24''  union all
select  ''0511-02-0318'',   ''Salvador'',   9.28,   ''2015-09-10''  union all
select  ''0415-02-0960'',   ''Martin'', 0,  ''2015-09-18''  union all
select  ''0415-02-0960'',   ''Martin'', 10.39,  ''2015-09-17''  union all
select  ''0415-02-0960'',   ''Martin'', 8.44,   ''2015-09-16''  union all
select  ''0415-02-0960'',   ''Martin'', 10.9,   ''2015-09-15''  union all
select  ''0415-02-0960'',   ''Martin'', 10.91,  ''2015-09-14''  union all
select  ''0415-02-0960'',   ''Martin'', 9.91,   ''2015-09-11''  union all
select  ''0415-02-0960'',   ''Martin'', 10.31,  ''2015-09-10''  union all
select  ''0415-02-0960'',   ''Martin'', 11.16,  ''2015-09-09''  union all
select  ''0415-02-0960'',   ''Martin'', 12.04,  ''2015-09-08''  union all
select  ''0415-02-0960'',   ''Martin'', 9.42,   ''2015-09-07''  union all
select  ''0415-02-0960'',   ''Martin'', 10.43,  ''2015-09-04''  union all
select  ''0415-02-0960'',   ''Martin'', 10.39,  ''2015-09-03'')data');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here's is my attempt output:

I want an output if pivot date is Equal to rd then the pivot date content is become the value of the total but if not equal then the value become 0. Also the id and lastname become distinct Also with the pivot date the total= should be remove.

I've tried on that sample commented code but no luck.

This one work but it return only one row.

GROUP_CONCAT(DISTINCT CONCAT("SUM(IF(data.rd='",DATE(list_of_dates),"',`total`,0)) AS `",list_of_dates,"`")) as `dates`

Any help is appreciated.

Thanks in advance

Answer 1

UPDATE: I changed part of the code to order the date sub total columns

I started out by creating a select statement that produces the final results, then wrote code to generate that select statement. I also created a SQL Fiddle to produce the statement and results.

Rather than fake the data in the procedure, I created a real table. You will have to modify the code to fit your particular needs since I didn't have the name of the table you would be using.

This works in several steps:

• Set the parameters (start/end dates)
• Get a list of dates from the data for the range specified.
• Generate the SQL statement.
• Execute the SQL statement.

Here's the test SQL statement I wrote as a model:

SELECT 
    `id`,
    `lastname`,
    SUM(`total`) AS `total`,
    SUM(if(`rd` = '2015-09-01',`total`,0)) AS `total:2015-09-01`,
    SUM(if(`rd` = '2015-09-02',`total`,0)) AS `total:2015-09-02`,
    SUM(if(`rd` = '2015-09-03',`total`,0)) AS `total:2015-09-03`,
    SUM(if(`rd` = '2015-09-04',`total`,0)) AS `total:2015-09-04`,
    SUM(if(`rd` = '2015-09-05',`total`,0)) AS `total:2015-09-05`,
    SUM(if(`rd` = '2015-09-06',`total`,0)) AS `total:2015-09-06`
FROM `test_table`
WHERE `rd` BETWEEN @sdate AND @edate
GROUP BY `id`,`lastname`
ORDER BY `id`,`lastname`

And here's the code to generate and run that SQL statement:

/* Set the parameters */
SET @sql = NULL;
SET @dates = NULL;
SET @sdate = '2015-09-01';
SET @edate = '2015-09-06';

/* Get a list of dates, and format as the column def for the SQL SELECT */
SELECT
    GROUP_CONCAT(CONCAT("SUM(IF(`rd` = '",DATE(`rd`),"',`total`,0)) AS `total:",DATE(`rd`),"`")) as `dates`
FROM (SELECT `rd` FROM `test_table` GROUP BY `rd` ORDER BY `rd`) AS a
WHERE a.`rd` BETWEEN @sdate AND @edate
INTO @dates;

/* Generate the SQL SELECT */
SELECT CONCAT("SELECT `id`,`lastname`,SUM(`total`) AS `total`,",@dates," FROM `test_table` WHERE `rd` BETWEEN @sdate AND @edate GROUP BY `id`,`lastname` ORDER BY `id`,`lastname`")
INTO @sql;

/* Execute the SELECT */
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;