SQL — вставка с использованием имени столбца в качестве значения

Я пытаюсь выполнить вставку в таблицу2 на основе выбора из таблицы1, но не могу получить правильный синтаксис. Имена столбцов из таблицы 1 будут управлять значением, вставляемым в столбец PD_NO в таблице 2, как показано в примере ниже. Может кто-нибудь помочь с этим?

Таблица 1:

         (1)     (2)     (3)     (4)     (5)     (6)
| SEQ | PD_01 | PD_02 | PD_03 | PD_04 | PD_05 | PD_06 |
|-----+-------+-------+-------+-------+-------+-------|
| 632 | 10000 |   0   |  500  |   0   | 20000 |   0   |

Таблица 2:

| SEQ | PD_NO |  AMT  |
|-----+-------+-------|
| 632 |   1   | 10000 |
|-----+-------+-------|
| 632 |   3   |  500  |
|-----+-------+-------|
| 632 |   5   | 20000 |
|-----+-------+-------|

Я знаю, работаю ли я в другом направлении (вставляя содержимое таблицы2 в таблицу1), я могу сделать что-то вроде следующего:

INSERT INTO table1
SELECT 
      seq,
      SUM (CASE WHEN pd_no = 1 THEN amt ELSE 0 END) p01_amt,
      SUM (CASE WHEN pd_no = 2 THEN amt ELSE 0 END) p02_amt,
      SUM (CASE WHEN pd_no = 3 THEN amt ELSE 0 END) p03_amt,
      SUM (CASE WHEN pd_no = 4 THEN amt ELSE 0 END) p04_amt,
      SUM (CASE WHEN pd_no = 5 THEN amt ELSE 0 END) p05_amt,
      SUM (CASE WHEN pd_no = 6 THEN amt ELSE 0 END) p06_amt
FROM table2;

sql oracle pivot-table select insert
person thefreeline    schedule 12.07.2013    source источник
comment
Пожалуйста, отметьте свой вопрос маркой используемой вами СУБД. Например. sql-server, mysql, oracle и т. д.   -  person Bill Karwin    schedule 12.07.2013

Ответы (4)


arrow_upward
4
arrow_downward

Это типичная проблема, для которой Oracle 11 предоставляет предложение UNPIVOT для использования в запросах:

insert  into table2(seq, pd_no, amt)
select  seq, pd_no, amt
from    ( select  *
          from    table1
          unpivot (amt for pd_no in (pd_01 as 1, pd_02 as 2, pd_03 as 3, pd_04 as 4, pd_05 as 5, pd_06 as 6))
        );
person Tomasz Żuk    schedule 12.07.2013
comment
Томаш, спасибо за это. Это именно то, что я искал. - person thefreeline; 15.07.2013

arrow_upward
2
arrow_downward

В чистом sql это можно сделать так:

INSERT INTO table2 ( SEQ , PD_NO,  AMT )
SELECT SEQ, 1 as pd_no, PD_01 FROM Table1
UNION ALL
SELECT SEQ, 2 as pd_no, PD_02 FROM Table1
UNION ALL
SELECT SEQ, 3 as pd_no, PD_03 FROM Table1
UNION ALL
SELECT SEQ, 4 as pd_no, PD_04 FROM Table1
UNION ALL
SELECT SEQ, 5 as pd_no, PD_05 FROM Table1
UNION ALL
SELECT SEQ, 6 as pd_no, PD_06 FROM Table1

Некоторые базы данных имеют оптимизированные команды, которые считывают исходную таблицу только один раз (приведенный выше запрос считывает исходную таблицу 6 раз), например в ORACLE:

INSERT ALL
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 1, PD_01 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 2, PD_02 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 3, PD_03 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 4, PD_04 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 5, PD_05 )
INTO table2 ( SEQ , PD_NO,  AMT ) VALUES ( seq, 6, PD_06 )
SELECT * FROM table1
person krokodilko    schedule 12.07.2013

arrow_upward
0
arrow_downward

insert ... select from работает на основе строк 1:1: одна строка данных из таблицы «выбрать» переходит в одну строку таблицы «вставка». Вы пытаетесь взять одну строку из исходной таблицы и превратить ее в несколько строк в целевой таблице. Это невозможно напрямую. Вам нужно будет запустить несколько вставок/выборов, по одному для каждого поля, которое вы хотите разделить:

INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_01, AMT FROM table1
INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_02, AMT FROM table1
INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_03, AMT FROM table1
INSERT INTO table2 (SEQ, PD_NO, AMT) SELECT SEQ, PD_04, AMT FROM table1
etc...
person Marc B    schedule 12.07.2013

arrow_upward
0
arrow_downward 

CREATE TABLE 1to6 (i INT PRIMARY KEY);
INSERT INTO 1to6 (i) VALUES (1), (2), (3), (4), (5), (6);

INSERT INTO table2 (seq, pd_no, amt)
SELECT seq, i, 
 CASE(i)
 WHEN 1 THEN pd_01
 WHEN 2 THEN pd_02
 WHEN 3 THEN pd_03
 WHEN 4 THEN pd_04
 WHEN 5 THEN pd_05
 WHEN 6 THEN pd_06
 END
FROM table1 CROSS JOIN 1to6

Комментарий: Очевидно, Oracle не поддерживает многострочный синтаксис INSERT (несмотря на то, что этот синтаксис является стандартным SQL-99). Вы можете вставлять по одной строке за раз:

INSERT INTO 1to6 (i) VALUES (1);
INSERT INTO 1to6 (i) VALUES (2);
INSERT INTO 1to6 (i) VALUES (3);
INSERT INTO 1to6 (i) VALUES (4);
INSERT INTO 1to6 (i) VALUES (5);
INSERT INTO 1to6 (i) VALUES (6);
person Bill Karwin    schedule 12.07.2013
comment
Этот вариант кажется очень чистым. Однако при попытке вставить значения в строку 2 я получаю сообщение о неправильном окончании команды SQL. Удаление , (2), (3) и т. д. работает нормально и дает мне таблицу с одним столбцом (i) и значение 1. Любые мысли? - person thefreeline; 12.07.2013