title: how to use if statement in Group_Concat in Mysql
body: <p>After my head bangs to the wall, I am trying to have a pivot data with this sample table content.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is my working attemp Query but i think i missed up something:</p>
  
  <pre><code>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 &lt; 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;
  </code></pre>
  
  <p>Here's is my attempt output:</p>
  
  <p><a href="https://i.stack.imgur.com/9aNL3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9aNL3.png" alt="enter image description here"></a></p>
  
  <p>I want an output if <code>pivot date</code> is Equal to <code>rd</code> then the pivot <code>date content</code> is become the <code>value</code> of the <code>total</code> but if not equal then the value become 0. Also the <code>id and lastname become distinct</code> Also with the pivot date the <code>total=</code> should be remove.</p>
  
  <p>I've tried on that sample commented code but no luck.</p>
  
  <p>This one work but it return only one row.</p>
  
  <pre><code>GROUP_CONCAT(DISTINCT CONCAT("SUM(IF(data.rd='",DATE(list_of_dates),"',`total`,0)) AS `",list_of_dates,"`")) as `dates`
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/25gVb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/25gVb.png" alt="enter image description here"></a></p>
  
  <p>Any help is appreciated.</p>
  
  <p>Thanks in advance</p>