I am trying to generate a dynamic column based on the existing projects in my projects table, so I want to collect the user time he spends on each project based on another table that tracks records for users on each project. The problem is when I run the query in MySQL, I received error: PREPARE stmt FROM @sql Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM tracker.team_members tm JOIN tracker.teams t ON tm.te’ at line 6.

The query:

SET @startDate = '2024-01-01';
SET @endDate = '2024-12-31';

SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'SUM(CASE WHEN pr.PRJ_NAME = '', pr.PRJ_NAME, '' THEN TIMESTAMPDIFF(MINUTE, utt.uttStart, utt.uttEnd) ELSE 0 END) AS `', pr.PRJ_NAME, '`'
        )
    ) INTO @sql
FROM 
    tracker.user_time_tracking utt
JOIN 
    tracker.Projects pr ON utt.prj_Id = pr.prj_id
WHERE utt.utt_Start >= @startDate AND utt.utt_End <= @endDate;

SET @sql = CONCAT('
    SELECT 
        t.name AS TeamName, 
        u.username AS MemberName, 
        p.position_name AS PositionName, 
        ', @sql, '
    FROM 
        tracker.team_members tm
    JOIN 
        tracker.teams t ON tm.team_id = t.team_id
    JOIN 
        Info.Users u ON tm.USR_NAME = u.USR_NAME
    JOIN 
        tracker.Positions p ON u.USR_POSITION_ID = p.POSITION_ID
    LEFT JOIN 
        tracker.user_time_tracking utt ON u.USR_NAME = utt.USR_NAME
    LEFT JOIN 
        tracker.Projects pr ON utt.PRJ_ID = pr.PRJ_ID
    WHERE utt.utt_Start >= '', @startDate, '' AND utt.utt_End <= '', @endDate, ''
    GROUP BY 
        t.name, u.username, p.position_name
    ORDER BY 
        t.name, u.username;
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Can you help me to solve this error in MySQL, I also want to use it in my spring boot application, and I appreciate any advice on it.

I tried to run the query in MySQL, but I received an error. And when I tried to use select @sql, I received only one column @sql with one record that is the same query. I expect to retrieve a table with a column for member name and his team’s name and more columns includes projects columns with the time he spent on each one.

First Result

Second Result