CODEWITHSUNDEEP
mysqlsqlconcatenationgroup-concat
Azima
Azima

Reputation: 4151

concat result values from pivoting mysql

I am trying to get a concatenated string value after pivoting.

Currently, there's only a single value and is working fine.

Here's the query:

DROP PROCEDURE IF EXISTS getAllUserLunchReport;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUserLunchReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
BEGIN

SET GLOBAL group_concat_max_len=4294967295;
SET @SQL = NULL;
SET @start_date = DATE(start_date);
SET @end_date = DATE(end_date);

    SELECT
    COALESCE(GROUP_CONCAT(DISTINCT
    CONCAT(
    'SUM(CASE WHEN date = "',DATE(issuedDateTime),'" AND lunchStatus = 1 THEN (SELECT CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), "," ,(SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))) ELSE 0 END) AS `',DATE(issuedDateTime),'`'
    ) ORDER BY issuedDateTime
), '0 as `NoMatchingRows`') INTO @SQL

        FROM `lunch_status` 
        WHERE DATE(issuedDateTime) BETWEEN @start_date AND @end_date;

    SET @SQL 
    = CONCAT
    (
    '
        SELECT concat(u.firstname," ",u.lastname) as Employee, w.*
        FROM users u
        INNER JOIN
        (SELECT userId, ', @SQL, ' 
        FROM
        (
            SELECT userId, lunchStatus, DATE(issuedDateTime) as date 
            FROM `lunch_status` 
            WHERE DATE(issuedDateTime) BETWEEN "',@start_date,'" AND "',@end_date,'" 
        ) as a
        GROUP BY userId) w
        ON u.id = w.userId
        ORDER BY Employee;
    '
    );

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

I am only getting a first value, without other value that I am trying to concatenate.

Here's how I expected the outcome:

employee    |   2019-1-15  |  2019-1-16
----------------------------------------
Jack        |   30,140     |  30,140

Executing the concatenation query separately works though.

NOTE: in the stored procedure above, values are concatenated if separator is removed. CONCAT((select ...), (select...)) gives 30140.

Could it be the issue with quotes, but I have tried every way.

FYI, here's what @SQL yields after the first query:

SUM(CASE WHEN date = "2019-01-15" AND lunchStatus = 1 THEN (SELECT CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), ",", (SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))) ELSE 0 END) AS `2019-01-15`,
SUM(CASE WHEN date = "2019-01-16" AND lunchStatus = 1 THEN (SELECT CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), ",", (SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))) ELSE 0 END) AS `2019-01-16`

looks valid.

EDIT: it seems like it is working fine with "integer" values. e.g.

CONCAT((SELECT rate FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), "111" ,(SELECT rate FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1))

gives 301140.

But with other characters ,/./-/_, only the first parameter is displayed : 30.

Upvotes: 0

Views: 53

Answers (1)

Nick
Nick

Reputation: 147216

The reason that your code doesn't work when you include the , in the CONCAT is because that CONCAT is inside a SUM, and it won't be able to interpret 30,140 as a number, where it can do so for 30140 (hence with no separator you get a result). Without knowing more details of your table data and desired results it's hard to tell exactly how to fix the problem, but perhaps you want something like this:

CASE WHEN date = "2019-01-15" AND lunchStatus = 1 THEN CONCAT((SELECT SUM(rate) FROM lunch_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1), ",", (SELECT SUM(rate) FROM lunch_gbd_rate WHERE DATE(created_on) <= date ORDER BY created_on DESC LIMIT 1)) ELSE 0 END) AS `2019-01-15`

Upvotes: 1

Related Questions