在mysql中将多行作为单行复制到另一个表中 [英] Copying multiple rows into another table as a single row in mysql

问题描述

我一直在寻找这个问题的答案，但找不到

I have been searching for an answer to this problem and cant find one

我在 LAMP 堆栈上使用我的 sql 和 Drupal

I am using my sql with Drupal on a LAMP stack

首先，如果这在 cron 工作中更好，那么请告诉我

first, if this is better in a cron job then let me know

我的问题

我有一个必须使用的表单模块.它保存数据如下:

I have a form module that I must use. It saves the data like:

ID, element_id, group_id, element_value
1            0         1           Name
2            1         1        Address
3            2         1            DOB    etc...

我希望能够获取元素值并将它们放入另一个表中的 1 行

I want to be able to take the element values and put them into 1 row in another table

所以我现在有

INSERT INTO new_table (name, street_address,street_address_line_2, city, state, zip,
                       country, dob)
SELECT element_value 
FROM submited_table
WHERE group_id = 'some_group_id'

我不知道如何将不同的值放在同一行中以创建单个记录.

I am stuck as to how to place the different values in the same row to make a single record.

有人可以帮忙解决这个问题吗?

Can anyone help with with this?

推荐答案

一种方法是通过聚合:

INSERT INTO new_table (name, address, dob)
    SELECT max(case when element_id = 1 then element_value end) as name,
           max(case when element_id = 2 then element_value end) as address,
           max(case when element_id = 3 then element_value end) as DOB,
           . . .
    FROM submited_table
    WHERE group_id = 'some_group_id';

这假设 element_id 正在识别第一个表中的每一行所指的内容.它还假设每个元素值对应于目标表中的一列.

This assumes that element_id is identifying what each row in the first table refers to. It also assumes that each element value corresponds to a column in the target table.

这篇关于在mysql中将多行作为单行复制到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！