CODEWITHSUNDEEP
mysqlsql
Allen Huang
Allen Huang

Reputation: 394

Combine Query results into one comma separated entry

I am fairly new to SQL and im not sure if this is possible, but as the title suggests I want to combine the results of a query into one "entity" that I can then later insert into another table. Here is an example:

SELECT COLUMN1
FROM TABLE1

Let's say the output is this:

COLUMN1
-------
data1
data2
data3

What I want to do is to take the result of this query and turn it into this:

data1,data2,data3

Which I can then insert into another table as one row like this:

INSERT INTO TABLE2(RESULT)
VALUES ('data1,data2,data3')

Upvotes: 0

Views: 1176

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use GROUP_CONCAT to get a list of all your column values:

SELECT GROUP_CONCAT(COLUMN1 SEPARATOR ',') FROM TABLE1

demo: http://sqlfiddle.com/#!9/3cf4ecc/5/0

To directly INSERT the result of this SELECT you can use the following:

INSERT INTO TABLE2 SELECT GROUP_CONCAT(COLUMN1 SEPARATOR ',') FROM TABLE1

demo: http://sqlfiddle.com/#!9/466869/1/1

You can also use a VIEW to keep your tables normalized:

CREATE VIEW V_TABLE1 AS SELECT GROUP_CONCAT(COLUMN1 SEPARATOR ',') FROM TABLE1;

The advantage of a VIEW is that it is always generated from the current values of your table.

... but be careful: As marc_s already mentioned in the comments, it is not recommended to store multiple values as a list in a single column. You should always design normalized databases.

Upvotes: 1

Adam
Adam

Reputation: 525

What you need is an aggregate function such as string_agg in postgresql , simply as SELECT string_agg(COLUMN1, ',') FROM TABLE1;

Certainly you can combine it with INSERT, i.e INSERT ...SELECT.

Upvotes: 0

Related Questions