SELECT a.ID, a.Campaign_ID, a.FirstName, a.LastName, MAX(CASE WHEN b.data = ‘qwerty1’ THEN b.Name END) qwerty1, MAX(CASE WHEN b.data = ‘qwerty2’ THEN b.Name END) qwerty2, MAX(CASE WHEN b.data = ‘qwerty3’ THEN b.Name END) qwerty3 FROM Participants a INNER JOIN Participants_Custom b ON a.ID = b.Participant_ID GROUP BY a.ID, a.Campaign_ID, a.FirstName, a.LastName
由于的值data未知，因此 动态sql 更为可取。
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN b.data = ''',
data,
''' THEN b.Name ELSE NULL END) AS ',
CONCAT('`',data, '`')
)
) INTO @sql
FROM Participants_Custom;
SET @sql = CONCAT('SELECT a.ID,
a.Campaign_ID,
a.FirstName,
a.LastName,', @sql,
'FROM Participants a
INNER JOIN Participants_Custom b
ON a.ID = b.Participant_ID
GROUP BY a.ID,
a.Campaign_ID,
a.FirstName,
a.LastName');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
602392714
清零编程群