SQL - Transform different values of a column into several columns with count in each of them

2

My question is how to separate different values of a column into several columns and give a count on each item according to the name in postgres, follow the 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 
    
asked by anonymous 03.03.2018 / 13:50

1 answer

1

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
    
03.03.2018 / 14:01