MySQL选择特定的行值作为列名 [英] MySQL select specific row values as column names

问题描述

这是我的基本participants表(显示为关联数组):

Here is my basic participants table (displayed as associative array):

[id] => 1
[campaign_id] => 41
[firstname] => Jeff
[lastname] => Berube

在另一个名为participants_custom的表上，我可以添加属于participants行的多个自定义数据.像这样:

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

我当前正在创建join，但是它将唯一的name，participant_id和data添加到我的行中.我想要的是我的查询返回如下内容:

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

行值name成为列的地方，而value则是值. 我该怎么做?

Where row value name becomes a column, and value, it's value. How can I make it?

我找到了 this 和 this ，但不成功.我正在寻找一种适合我的情况的方法.感谢您的帮助.

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

• SQLFiddle演示
• SQLFiddle Demo

更新1

由于data的值未知，因此更优选动态sql .

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;

• SQLFiddle演示
• SQLFiddle Demo

这篇关于MySQL选择特定的行值作为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！