SQL query to insert multiple columns into one while duplicating the rest of the fields in each row

Question

Good afternoon! I am trying to take values that span multiple columns in one table and insert them into a single column in a new table, but for multiple different fields into a few different fields in the new table.

E.g. in my original table there are 5 columns for different medications, I am wanting to insert them into one column in the new table and just have the other column values duplicate in rows for each medication that was inserted.

The error message I get is that there are more expressions than targets, which yes there are, but some of the inserts are labeled the same. Does 'Insert' not allow for inserting multiple things into one column? Is there another method or function to use for this?

Thank you in advance!

This is my newly created table:

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;

And this is my code for inserting into that new table from the old one:

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

EDIT: I couldn't figure out how to format tables, so I uploaded two images of the results I'm hoping to see and what the original data would look like.

Original Table Image

New Table Image

update your question add a proper data sample and the expected result too — ScaisEdge
– ScaisEdge, Commented Sep 20, 2018 at 16:23
@scaisEdge thanks! I couldn't format it to look nicely, so I just uploaded two images that show the before and after of what I was hoping to see. — beatrixb
– beatrixb, Commented Sep 20, 2018 at 16:50

Laurenz Albe · Accepted Answer · 2018-09-20 17:14:11Z

I assume that you want to

CREATE TABLE tab (patient text, med text);

INSERT INTO tab (patient, med, med, med)
VALUES ('Joe', 'vomitol', 'putrefac', 'abrasil');

As you have found out, that won't work.

You can store several values in a column by using an array, though:

CREATE TABLE tab (patient integer, med text[]);

INSERT INTO tab (patient, med)
VALUES ('Joe', ARRAY['vomitol', 'putrefac', 'abrasil']);

However, that violates the first normal form for relational databases, and while violating the normal form is not a capital crime, it should be done with care.

My advice is to use arrays only if you don't have to use the array elements heavily inside the database, e.g. in a WHERE condition that you want to index. Basically, if it is a single value as far as the database is concerned, it is fine to use arrays.

If, however, your goal is to avoid redundant data in the database (you don't want to store the common columns several times), the solution is not to denormalize, but to normalize more:

CREATE TABLE tab_common (
   id integer PRIMARY KEY,
   patient text NOT NULL
);

CREATE TABLE tab_med (
   id integer PRIMARY KEY,
   common_id integer NOT NULL REFERENCES tab_common(id),
   med text NOT NULL
);

INSERT INTO tab_common (id, patient)
VALUES (1, 'Joe');

INSERT INTO tab_med (id, common_id, med)
VALUES (1, 1, 'vomitol'),
       (2, 1, 'putrefac'),
       (3, 1, 'abrasil');

Then your table conforms to the third normal form, and you should be fine.

ScaisEdge · Accepted Answer · 2018-09-20 17:07:31Z

could be using union for the single select you need

    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

and so on ...

Collectives™ on Stack Overflow

SQL query to insert multiple columns into one while duplicating the rest of the fields in each row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related