Cross tab query with concat field

Question

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;

Answer 1

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;