Query GROUP_CONCAT issue

Discussion in 'MySQL' started by dizyn, Jul 18, 2013.

0
  1. #1
    Need help in query group_concat issue, Here is my query
    
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(ps.name = ''',
      name,
      ''', ch.state, 0)) AS ',
      name,
      
      
      ',MAX(IF(ps.name = ''',
      name,
      ''', ch.data, NULL)) AS ',
      name,'_data', '_time'
        
        
    )
  )
FROM phases where phase_id > 7
    Code (markup):
    It returns me

    MAX(IF(ps.name = 'logistics_receiving', ch.state, 0)) AS logistics_receiving,MAX(IF(ps.name = 'logistics_receiving', ch.data, NULL)) AS logistics_receiving_data_time,MAX(IF(ps.name = 'logistics_buying', ch.state, 0)) AS logistics_buying,MAX(IF(ps.name = 'logistics_buying', ch.data, NULL)) AS logistics_buying_data_time,MAX(IF(ps.name = 'logistics_sku_generation', ch.state, 0)) AS logistics_sku_generation,MAX(IF(ps.name = 'logistics_sku_generation', ch.data, NULL)) AS logistics_sku_generation_data_time,MAX(IF(ps.name = 'logistics_quality_control', ch.state, 0)) AS logistics_quality_control,MAX(IF(ps.name = 'logistics_quality_control', ch.data, NULL)) AS logistics_quality_control_data_time,MAX(IF(ps.name = 'logistics_sorting', ch.state, 0)) AS logistics_sorting,MAX(IF(ps.name = 'logistics_sorting', ch.data, NULL)) AS logistics_sorting_data_time
    Code (markup):
    I want it to return me:
    MAX(IF(ps.name = 'logistics_receiving', ch.state, 0)) AS logistics_receiving,
    MAX(IF(ps.name = 'logistics_receiving', ch.data, NULL)) AS logistics_receiving_data,
    MAX(IF(ps.name = 'logistics_receiving', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp_in),NULL),NULL)) AS logistics_receiving_completion_time,

    MAX(IF(ps.name = 'logistics_buying', ch.state, 0)) AS logistics_buying,
    MAX(IF(ps.name = 'logistics_buying', ch.data, NULL)) AS logistics_buying_data,
    MAX(IF(ps.name = 'logistics_buying', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp_in),NULL),NULL)) AS logistics_buying_completion_time,

    MAX(IF(ps.name = 'logistics_sku_generation', ch.state, 0)) AS logistics_sku_generation,
    MAX(IF(ps.name = 'logistics_sku_generation', ch.data, NULL)) AS logistics_sku_generation_data,
    MAX(IF(ps.name = 'logistics_quality_control', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp_in),NULL),NULL)) AS logistics_quality_control_time,

    MAX(IF(ps.name = 'logistics_quality_control', ch.state, 0)) AS logistics_quality_control,
    MAX(IF(ps.name = 'logistics_quality_control', ch.data, NULL)) AS logistics_quality_control_data,
    MAX(IF(ps.name = 'logistics_sorting', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp_in),NULL),NULL)) AS logistics_sorting_time,

    MAX(IF(ps.name = 'logistics_sorting', ch.state, 0)) AS logistics_sorting,
    MAX(IF(ps.name = 'logistics_sorting', ch.data, NULL)) AS logistics_sorting_data
    MAX(IF(ps.name = 'logistics_sorting', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp_in),NULL),NULL)) AS logistics_sorting_time,


    Need help please
     
    dizyn, Jul 18, 2013 IP