Digamos que tengo la siguiente tabla:
Id color A00 blue A00 blue A99 red A99 blue A95 yellow A97 greenMe gustaría obtener algo como:
Id blue red yellow green A00 2 0 0 0 A99 1 1 0 0 A95 0 0 1 0 A97 0 0 0 1¿Cuál es la forma más fácil de hacer esto?
Pensé en esto:
select Id, sum(case when color='blue' then 1 else 0 end) as blue, sum(case when color='red' then 1 else 0 end) as red, . . . from tableEl problema es que tengo tantos colores que hacer esto sería agotador. hay una manera mas facil?
Hay muchas maneras de lograr esto:
USO DE FILTRO
select id, count(*) filter (where color='blue') as "Blue", count(*) filter (where color='red') as "Red", count(*) filter (where color='yellow') as "Yellow", count(*) filter (where color='green') as "Green" from samp group by idtu método
select id, sum(case when color='blue' then 1 else 0 end) as "Blue", sum(case when color='red' then 1 else 0 end) as "Red", sum(case when color='yellow' then 1 else 0 end) as "Yellow", sum(case when color='green' then 1 else 0 end) as "Green" from samp group by idUso de tabulaciones cruzadas
select * from crosstab( 'select id, color,count(*) from samp group by id,color order by id,color', 'select distinct color from samp order by color' ) as ct("ID" varchar, "blue" int,"green" int,"red" int,"yellow" int);Nota: debe crear una extensión para la tabla de referencias cruzadas utilizando la consulta a continuación
CREATE EXTENSION IF NOT EXISTS tablefunc;