Group By, and create new columns for the PostgreSQL result

Question

Group By, and create new columns for the PostgreSQL result

Navigation

#1 by (2 votes)

2

I have the following problem: I have my table access

In this table I relate the Role with a Module and the Permission it has.

What I want is to create a view that returns the data to me in the following way:

View returning the data.

I want to group by Modules and return in 3 new columns 1 if you have that permission or 0 if you do not have it.

Try to do it this way but do not group me by modules

SELECT DISTINCT
  modulo,
  case when permiso LIKE 'A' then 1 else 0 end AS p1,
  case when permiso LIKE 'C' then 1 else 0 end AS p2,
  case when permiso LIKE 'M' then 1 else 0 end AS p3
from acceso
group by modulo, permiso

I appreciate your help.

postgresql group-by agrupacion

asked by Yulian David 23.05.2018 в 23:28

source

1 answer

Write doubles with fwrite in c download an element from the listView as footer

score 2 · Accepted Answer

SELECT modulo,
       MAX(case when permiso = 'A' then 1 else 0 end) AS p1,
       MAX(case when permiso = 'C' then 1 else 0 end) AS p2,
       MAX(case when permiso = 'M' then 1 else 0 end) AS p3
       from acceso
       group by modulo

Comments:

The grouping must be only per module, so the CASE must be within an aggregation function.
If you use the GROUP BY it makes little sense to use DISTINCT