multiple column to onec column

zab08 <zab08@126.com> wrote:

I have two table:

CREATE TABLE roles(role_name varchar(255) primary key);

CREATE TABLE roles_permissions(permission varchar(100), role_name varchar
(100));

here is result by :
SELECT * from roles;
role_name
-----------
role1
role2
(2 rows)

here is result by :
SELECT * from roles_permissions ;
role_name | permission
-----------+-------------
role1 | permission1
role1 | permission2
role2 | permission1
(3 rows)

---------------------------------------- -----------------------------------

after the command:
SELECT * from roles, roles_permissions;

That's a CROSS-JOIN

role_name | role_name | permission
-----------+-----------+-------------
role1 | role1 | permission1
role1 | role1 | permission2
role1 | role2 | permission1
role2 | role1 | permission1
role2 | role1 | permission2
role2 | role2 | permission1

the expected result is:
role_name | permission
-----------+-------------
role1 | permission1,permission2,
& nbsp;role2 | permission1

for short:
afer join of two table, here is some repeat columns,

yeah, it's a cross-join. Rewrite your query, adding a WHERE-condition:

where roles.role_name = roles_permissions.role_name

Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

thanks , this is a example of my application.

the ans is here, http://www.postgresql.org/docs/9.0/static/xaggr.html

sql command:

CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);

SELECT r.role_name, array_accum(permission) from roles r, roles_permissions rp group by r.role_name;

and the result:

role_name | array_accum

-----------+---------------------------------------

role2 | {permission1,permission2,permission1}

role1 | {permission1,permission2,permission1}