0

I'm working whit a select and I saw that using pivot I can have some type of rows like columns. I'm triying have this select:

SELECT cosa.nombre, pais.codigo, asociacion.estado FROM cosa, pais, asociacion WHERE cosa.id = asociacion.id_cosa AND asociacion.pais = pais.id

"asociacion" is a m-n relational table that have the relation between "pais" and "cosa"...

that returns:

nombre  codigo  estado  
soli1   ES       1
soli1   IT       0
otro2   ES       0

In something like...

nombre ES IT
soli1  1  0
otro2  0  0

Using pivot...

I was working all day learning use pivot, but i have no idea how to implement in this case... If tried this in MySql server (2019) and Oracle (11) database server...

If someone that have experience whit PIVOT could help me transforming this select... it would be a big advance in my problem...

Alfonso Soria Muñoz
  • 1
  • 1

1 Answers1

0

Try following query in oracle: (your_table should be inner query and it should be your original query as mentioned in question)

Select * from
(Select nombre, codigo, estado
From your_table)
Pivot
(Max(estado) for codigo in ('ES', 'IT');

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31