Vista de datos por año para cobertura sig

[horacionomade]

Gracias al invaluable aporte y acompañamiento de gnzsoloyo ("... eso se llama tabla inversa") encontré la solución a mi problema, con el siguiente query, las ordenes "view", "case when" y "sum" logramos armar una salida csv para incorporar a una vista de un sistema de información geográfico!!

Mil gracias!!! GONZALO!!!!

Código MySQL:

/*Invertir filas en columnas*/
create OR REPLACE view expedientes_historial as (
 
    SELECT Provincia,anio, COUNT(*) TotlaPOrPeriodo
    FROM territor_pgestionbdtmp.expedientes_historicos
    GROUP BY idjurisbahra, anio
);
 
create OR REPLACE view expedientes_historial_inversa as (
  select
    expedientes_historial.*,
    case when anio = "2002" then TotlaPOrPeriodo end as '2002',
    case when anio = "2003" then TotlaPOrPeriodo end as '2003',
    case when anio = "2005" then TotlaPOrPeriodo end as '2005',
    case when anio = "2006" then TotlaPOrPeriodo end as '2006',
    case when anio = "2007" then TotlaPOrPeriodo end as '2007',
    case when anio = "2008" then TotlaPOrPeriodo end as '2008',
    case when anio = "2009" then TotlaPOrPeriodo end as '2009',
    case when anio = "2010" then TotlaPOrPeriodo end as '2010',
    case when anio = "2011" then TotlaPOrPeriodo end as '2011',
    case when anio = "2012" then TotlaPOrPeriodo end as '2012',
    case when anio = "2013" then TotlaPOrPeriodo end as '2013',
    case when anio = "2014" then TotlaPOrPeriodo end as '2014',
    case when anio = "2015" then TotlaPOrPeriodo end as '2015'
  from expedientes_historial
order by Provincia, anio
);
/*
Select * from expedientes_historial_inversa
*/
create OR REPLACE view expedientes_historial_cosmetica as (
  select
    Provincia,
    sum(expedientes_historial_inversa.2002) as '2002',
    sum(expedientes_historial_inversa.2003) as '2003',
    sum(expedientes_historial_inversa.2005) as '2005',
    sum(expedientes_historial_inversa.2006) as '2006',
    sum(expedientes_historial_inversa.2007) as '2007',
    sum(expedientes_historial_inversa.2008) as '2008',
    sum(expedientes_historial_inversa.2009) as '2009',
    sum(expedientes_historial_inversa.2010) as '2010',
    sum(expedientes_historial_inversa.2011) as '2011',
    sum(expedientes_historial_inversa.2012) as '2012',
    sum(expedientes_historial_inversa.2013) as '2013',
    sum(expedientes_historial_inversa.2014) as '2014',
    sum(expedientes_historial_inversa.2015) as '2015'
  from expedientes_historial_inversa
  group by Provincia
);
 
Select * from expedientes_historial_cosmetica
 
/*FIN*/
 

Mil Gracias Libras!!! Gonzalo ya me orientó más que bien!!

Nos leemos!!