时间戳

首页 > 解决方案 > SQL查询将多列插入一列，同时复制每一行中的其余字段

postgresql - SQL查询将多列插入一列，同时复制每一行中的其余字段

问题描述

下午好！我正在尝试将跨越一个表中的多个列的值插入到新表中的单个列中，但是对于多个不同的字段到新表中的几个不同字段中。

例如，在我的原始表中，有 5 列用于不同的药物，我想将它们插入到新表的一列中，而对于插入的每种药物，其他列值在行中重复。

我得到的错误消息是表达式多于目标，是的，但有些插入标记相同。“插入”是否不允许将多个内容插入一列？是否有其他方法或功能可用于此？

先感谢您！

这是我新创建的表：

CREATE TABLE source.roster_new
(
person_number text COLLATE pg_catalog."default",
date_of_birth timestamp without time zone,
person_last_name text COLLATE pg_catalog."default",
person_first_name text COLLATE pg_catalog."default",
dx_code text COLLATE pg_catalog."default",
dx_description text COLLATE pg_catalog."default",
dx_first_date timestamp without time zone,
gender text COLLATE pg_catalog."default",
age numeric,
last_test_type text COLLATE pg_catalog."default",
last_test_date timestamp without time zone,
medication text COLLATE pg_catalog."default"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

这是我从旧表插入新表的代码：

INSERT INTO source.roster_new
SELECT
person_number
,date_of_birth
,person_last_name 
,person_first_name 
,asthma_code AS dx_code
,copd_code AS dx_code
,asthma_description AS dx_description
,copd_description AS dx_description
,first_asthma_date AS dx_first_date
,first_copd_date AS dx_first_date
,gender 
,age 
,case when(last_flu_date IS NOT NULL) THEN 'Flu' End AS last_test_type
,case when(last_spiro_date IS NOT NULL) THEN 'Spirometry' End AS last_test_type
,case when(last_pneumo_date IS NOT NULL) THEN 'Pneumococcal' End AS last_test_type
,last_flu_date AS last_test_date
,last_spiro_date AS last_test_date
,last_pneumo_date AS last_test_date
,medication1 AS medication
,medication2 AS medication
,medication3 AS medication
,medication4 AS medication
,medication5 AS medication
FROM source.roster

编辑：我不知道如何格式化表格，所以我上传了两张我希望看到的结果图像以及原始数据的样子。

原始表格图像

新表格图像

标签： postgresqlinsertmultiple-columns

解决方案

可以为您需要的单一选择使用联合 

    INSERT INTO source.roster_new ( 
        person_number
        ,date_of_birth
        ,person_last_name 
        ,person_first_name 
        ,asthma_code AS dx_code
        ,dx_description
        ,dx_first_date
        ,gender 
        ,age 
        ,last_test_type
        , last_test_date
        ,medication )

    SELECT 
        person_number
        ,date_of_birth
        ,person_last_name 
        ,person_first_name 
        ,asthma_code AS dx_code
        ,asthma_description AS dx_description
        ,first_asthma_date AS dx_first_date
        ,gender 
        ,age 
        ,'Flu' End AS last_test_type
        ,last_flu_date AS last_test_date
        ,medication1 AS medication
    FROM source.roster
    where last_flu_date IS NOT NULL
    UNION 
    SELECT
        person_number
        ,date_of_birth
        ,person_last_name 
        ,person_first_name 
        ,asthma_code AS dx_code
        ,asthma_description AS 
        ,first_asthma_date AS 
        ,gender 
        ,age 
        ,'Spirometry' 
        ,last_spiro_date
        ,medication2
    FROM source.roster
    where last_spiro_date IS NOT NULL
    UNION 
    SELECT
        person_number
        ,date_of_birth
        ,person_last_name 
        ,person_first_name 
        ,asthma_code AS dx_code
        ,asthma_description AS 
        ,first_asthma_date AS 
        ,gender 
        ,age 
        ,'last_pneumo_date' 
        ,last_pneumo_date
        ,medication3
    FROM source.roster
    where last_pneumo_date

等等 ...

推荐阅读