Postgres Pro
Downloads
Home   >   mailing lists

Thread: multiple column to onec column

multiple column to onec column

From
zab08
Date:

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;
 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,

I only want to a row for role1.







Re: multiple column to onec column

From
Andreas Kretschmer
Date:
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°

Re: multiple column to onec column

From
zab08
Date:
thanks , this is a example of my application.


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}