Here is my basic participants table (displayed as associative array):
[id] => 1
[campaign_id] => 41
[firstname] => Jeff
[lastname] => Berube
On another table, named participants_custom, I can add multiple custom data, that belongs to a participants row. Like this:
[id] => 51
[participant_id] => 1
[name] => textfield_bh423vjhgv
[data] => qwerty1
[id] => 52
[participant_id] => 1
[name] => textfield_IDRr2kzjZR59Xjw
[data] => qwerty2
[id] => 53
[participant_id] => 1
[name] => textfield_6kj5bhjjg
[data] => qwerty3
I am currently making a join, but it adds the only name, participant_idand data to my row. What I want is my query to return something like this:
[id] => 1
[campaign_id] => 41
[firstname] => Jeff
[lastname] => Berube
[textfield_bh423vjhgv] => qwerty1
[textfield_IDRr2kzjZR59Xjw] => qwerty2
[textfield_6kj5bhjjg] => qwerty3
Where row value name becomes a column, and value, it's value. How can I make it?
I found this and this, which was unsuccessful. I'm finding a way that would work with my situation. Thanks for any help.
解决方案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
UPDATE 1
Since the values of data are unknown, a dynamic sql is much preferred.
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;
扫一扫
427
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
