CODEWITHSUNDEEP
mysql
Greg Fennell
Greg Fennell

Reputation: 176

MySql view with one column being an array from a table column

I'm trying to create a mysql view that pulls multiple values from one table into just one column in the view. I'm trying a different approach for the alias table used by postfix so that I can manage the additions and removals better for any given alias

Table

[email protected]         [email protected]
[email protected]          [email protected]
[email protected]         [email protected]

View

[email protected]       [email protected],[email protected]
[email protected]       [email protected]

What would the mysql view code be to create this view? Thanks for your time and responses

Upvotes: 0

Views: 497

Answers (3)

dhalsumit
dhalsumit

Reputation: 143

You can use group_concat while defining the view 

select column2, group_concat(column1)
from table1
group by column2

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Upvotes: 0

user2355419
user2355419

Reputation: 1

Create Table:

create table email (
  id serial,
  address varchar(254),
  alias varchar(254)
);

Select Statement:

select group_concat(address) as addresses, alias
from email
group by alias;

Create View:

create view aliases as
select group_concat(address) as addresses, alias
from email group by alias;

Upvotes: 0

Kermit
Kermit

Reputation: 34063

You're looking for GROUP_CONCAT:

CREATE VIEW view1 AS
SELECT email1,
    GROUP_CONCAT(email2) email2
FROM tbl1
GROUP BY email1

Result

|       EMAIL1 |               EMAIL2 |
---------------------------------------
| [email protected] | [email protected],[email protected] |
| [email protected] |           [email protected] |

See a demo

Upvotes: 3

Related Questions