1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query GROUP_CONCAT issue

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

  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