1

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;

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.