postgresql 行轉列，拼接字串

標籤：

create table k_user (   op_id                VARCHAR(36)          not null,   op_name              VARCHAR(100)         not null,   password             VARCHAR(100)         not null,   real_name            VARCHAR(100)         not null,   lock_mark            VARCHAR(50)          not null,   dept_no              TEXT                 null,   post_id              TEXT                 null,   tel                  VARCHAR(30)          null,   mail                 VARCHAR(50)          null,   create_id            VARCHAR(36)          not null,   create_name          VARCHAR(100)         not null,   create_time          VARCHAR(17)          not null,   modify_id            VARCHAR(36)          null,   modify_name          VARCHAR(100)         null,   modify_time          VARCHAR(17)          null,   remark               VARCHAR(1024)        null,   constraint PK_K_USER primary key (op_id));

create table k_user_role (   user_id              VARCHAR(36)          not null,   role_id              VARCHAR(36)          not null,   constraint PK_K_USER_ROLE primary key (user_id, role_id));

create table k_role (   op_id                VARCHAR(36)          not null,   op_name              VARCHAR(100)         not null,   create_id            VARCHAR(36)          not null,   create_name          VARCHAR(100)         not null,   create_time          VARCHAR(17)          not null,   modify_id            VARCHAR(36)          null,   modify_name          VARCHAR(100)         null,   modify_time          VARCHAR(17)          null,   remark               VARCHAR(1024)        null,   constraint PK_K_ROLE primary key (op_id));

select * from (select u.*,     array_to_string ( ARRAY ( SELECT  role_id FROM k_user_role ur WHERE ur.user_id = u.op_id ),‘,‘ ) AS user_role,     array_to_string ( ARRAY ( SELECT  op_name FROM k_user_role ur,k_role r WHERE ur.role_id=r.op_id and ur.user_id = u.op_id ),‘,‘ ) AS role_name FROM  k_user u ) k_user  limit 15 offset 0

 

postgresql 行轉列，拼接字串