Mysql – Cross tab query with concat field

MySQLpivot

I want to create a cross tab query for my table. When I use CONCAT I am unable to get output successfully. Can anyone help me with my query?

    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('CASE WHEN TX_STOP_NAME=''', TX_STOP_NAME, '''  THEN 
    concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT 
    else null  END AS `', concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT , '`')) 
    INTO @sql
    FROM cmn_trip_statistics;
    SET @sql = CONCAT(' select *from(SELECT s.FKYS_TRIP_ID,s.FKYS_REPORT_TYPE,sum(s.NO_HALT_DUR) as totalhalt, ', @sql, '
    FROM cmn_trip_statistics s 
    group by s.FKYS_TRIP_ID,S.TX_STOP_NAME)T where FKYS_REPORT_TYPE=''HALT''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Best Answer

The default length of a GROUP_CONCAT is 1024.

Please run this

mysql> SET group_concat_max_len = 1048576;

before the query. This will allow for a 1M string as the maximum GROUP_CONCAT.

If you want every session going forwards to have this, add this to my.cnf

[mysqld]
group_concat_max_len = 1M

and run this

mysql> SET GLOBAL group_concat_max_len = 1048576;

MySQL Restart not required

As for the query itself, avoid using newline characters. Change to this:

    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('CASE WHEN TX_STOP_NAME=''', TX_STOP_NAME, '''  THEN 
    concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT 
    else null  END AS `', concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT , '`')) 
    INTO @sql
    FROM cmn_trip_statistics;
    SET @sql = CONCAT('select * from (SELECT s.FKYS_TRIP_ID,s.FKYS_REPORT_TYPE,sum(s.NO_HALT_DUR) as totalhalt, ', @sql);
    SET @sql = CONCAT(@sql,' FROM cmn_trip_statistics s group by');
    SET @sql = CONCAT(@sql,' s.FKYS_TRIP_ID,S.TX_STOP_NAME) T');
    SET @sql = CONCAT(@sql,' where FKYS_REPORT_TYPE=''HALT''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
Related Question