SQL - Transform different values of a Column into multiple columns with Count in each of them

Asked

Viewed 308 times

2

My question is how to separate different values of a column into several columns and give a Count in each item according to the name in postgres, follows example below

How are you:

--TIPO O.S.--Count----Nome

     1009 ||   1   || Lucas    
     1008 ||   2   || Lucas    
     1008 ||   2   || Juliana  
     1007 ||   2   || Juliana  
     1007 ||   2   || Lucas
     1008 ||   2   || Vanessa 
     1007 ||   3   || Vanessa 

SQL: select os.tipo_os,count(os.tipo_os), pe.nome_usuario 
from table_os as os, table_pessoas as pe 
where os.codigo_usuario = pe.codpessoa
group by os.tipo_os, pe.nome_usuario

How to Stay:

--1009----1008----1007----Nome

   1   ||   2  ||   2  || Lucas    
       ||   2  ||   2  || Juliana     
       ||   2  ||   3  || Vanessa
sql database postgresql
  • 1

    You want a Pivot Table, But in postgresql you have to tell what would be the columns, it does not get it dynamically. See: http://www.vertabelo.com/blog/technical-articles/creating-pivot-tables-in-postgresql-using-the-crosstab-function

  • 1

    Even though the understanding is a little more complicated than the result presented by Bruno, it seems to me that also works Rovann, but I did not test !!

1 answer

2


Here’s a possible solution:

SELECT COUNT(CASE WHEN OS.TIPO_OS = '1009' THEN OS.TIPO_OS END) AS '1009',
       COUNT(CASE WHEN OS.TIPO_OS = '1008' THEN OS.TIPO_OS END) AS '1008',
       COUNT(CASE WHEN OS.TIPO_OS = '1007' THEN OS.TIPO_OS END) AS '1007',
       PE.nome_usuario AS Nome
  FROM table_os AS OS
 INNER JOIN table_pessoas AS PE
    ON OS.codigo_usuario = PE.codpessoa
 GROUP BY PE.nome_usuario

  • It worked well, I did not know this type of condition, even more used within the Count, but is fully functional !!

Browser other questions tagged sql database postgresql

You are not signed in. Login or sign up in order to post.