动态sql，如何将查询结果保存到新的表中？

Question

下面是我的表结构，我想将其转换为另一种格式(从行类型转换为列类型：

StudentID | Mark | Subject
-------------------------
10        |46    |Java
--------------------------
10        |65    |C#
--------------------------
10        |79    |JavaScript
---------------------------
11        |66    |Java
--------------------------
11        |85    |C#
--------------------------
11        |99    |JavaScript
--------------------------

我需要它像这样

StudentID | Java | C# | JavaScript
---------------------------------
10        |  46  | 65 |   79
---------------------------------
11        |  66  | 85 |  99
-------------------------------

我编写了一个动态SQL查询来透视我的表：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when subject = ''',
      subject,
      ''' then mark else 0 end) AS `',
      subject, '`'
    )
  ) INTO @sql
FROM  yourtable;

SET @sql = CONCAT('SELECT studentid, ', @sql, ' 
                  from yourtable
                  group by studentid');

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

我的问题是:如何将结果保存到另一个表中？

Answer 1

对于透视结果，请尝试执行以下操作：

select 
    StudentId,
    max(case Subject when 'Java' then Mark end) as 'Java', 
    max(case Subject when 'C#' then Mark end) as 'C#',
    max(case Subject when 'JavaScript' then Mark end) as 'JavaScript'
from
    marks_table
group by StudentId

要将输出插入到其他表中，请使用INSERT INTO ... SELECT...中的正确语法。

insert into target_table( col1, col2, ... )
select 
    StudentId,
    max(case Subject when 'Java' then Mark end) as 'Java', 
    max(case Subject when 'C#' then Mark end) as 'C#',
    max(case Subject when 'JavaScript' then Mark end) as 'JavaScript'
from
    marks_table
group by StudentId

修改‘插入...`并相应地使用。

编辑：

在动态创建的查询中

更改

SET @sql = CONCAT('SELECT studentid, ', @sql, ' 
                  from yourtable
                  group by studentid');

到的：

SET @insert_query = CONCAT( 'insert into target_table_name( ' );
SET @insert_query = CONCAT( @insert_query, 'col1, col2, col3, ... ) ' );

SET @sql = CONCAT( @insert_query, 'SELECT studentid, ', @sql, ' 
                  from yourtable
                  group by studentid');

编辑2：

如果想要从准备好的@sql select语句创建新的表pivot，可以将其与create table pivot as @sql一起使用。

示例：

set @sql := concat( 'create table pivot as ', @sql );

select @sql;

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

执行完上述代码后，运行以下语句，以查看sql引擎如何将列名及其数据类型声明为default。

mysql> desc pivot;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| studentid  | int(11)       | YES  |     | NULL    |       |
| Java       | decimal(32,0) | YES  |     | NULL    |       |
| C#         | decimal(32,0) | YES  |     | NULL    |       |
| JavaScript | decimal(32,0) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.08 sec)

mysql> select * from pivot;
+-----------+------+------+------------+
| studentid | Java | C#   | JavaScript |
+-----------+------+------+------------+
|        10 |   46 |   65 |         79 |
|        11 |   66 |   85 |         99 |
+-----------+------+------+------------+
2 rows in set (0.00 sec)

mysql> show create table pivot\G
*************************** 1. row ***************************
       Table: pivot
Create Table: CREATE TABLE `pivot` (
  `studentid` int(11) DEFAULT NULL,
  `Java` decimal(32,0) DEFAULT NULL,
  `C#` decimal(32,0) DEFAULT NULL,
  `JavaScript` decimal(32,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

示例@

Answer 2

编辑->我为MSSQL Server提供了答案，而不是MYSQL...

Ravinder在他的答案(投票)中是正确的。

很抱歉，如果任何人在MSSQL服务器上遇到此问题，请参考：

SELECT * into newtable FROM
( SELECT
    StudentId,
    max(case Subject when 'Java' then Mark end) as 'Java', 
    max(case Subject when 'C#' then Mark end) as 'C#',
    max(case Subject when 'JavaScript' then Mark end) as 'JavaScript'
from
    oldtable
group by StudentId) as importvalues;