Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Área de Tecnologías de la Información y las Comunicaciones Aplicadas Universidad de Murcia
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
ATICA arrow DBCONNECTOR arrow SQL - Pivot/Unpivot (cambiar filas por columas y viceversa)
22.11.2017
 
 
SQL - Pivot/Unpivot (cambiar filas por columas y viceversa) Imprimir E-mail

Desde Oracle 11g es posible "trasponer" (PIVOT) una consulta, de modo que lo que en una tabla/vista/consulta son resultados (parte de las filas) se pueden convertir en la columnas de otra consulta (nombres de las columnas).

De igual modo se puede hacer la operación contraria (UNPIVOT), convirtiendo las columnas de una consulta en las filas de otra.


Ejemplos

Empecemos con los ejemplos usando PIVOT. Supongamos que tenemos la siguiente consulta, que nos muestra el nº de empleados por año y tipo de trabajo:

select to_char(HIREDATE,'yyyy') anio, JOB, count(*)
  from emp group by to_char(HIREDATE,'yyyy'), JOB order by anio, job;

ANIO JOB       COUNT(*)
---- --------- --------
1980 CLERK        2
1981 ANALYST      1
1981 CLERK        1
1981 MANAGER      2
1981 SALESMAN     4
1987 ANALYST      1
1987 CLERK        1

Y queremos transformarla en otra que me muestre una tabla con el nº de empleados por año, mostrando una columna por cada tipo de trabajo (JOB):

select anio, ANALYST, CLERK, MANAGER, SALESMAN
  from  (select to_char(HIREDATE,'yyyy') anio, JOB from emp)
  pivot (count(*) for job in ('ANALYST' as ANALYST, 'CLERK' as CLERK,                               'MANAGER' as MANAGER, 'SALESMAN' as SALESMAN))
order by anio;

ANIO ANALYST      CLERK    MANAGER    SALESMAN
---- ---------- ---------- ---------- ----------
1980          0      2        0           0
1981          1      1        2           4
1987          1      1        0           0

Como se observa, en la nueva consulta (en marrón), la primera pasa a ser una subconsulta (en negrita), y se añade la claúsula PIVOT, que incluye una expresión de agregación (COUNT) y la definición de las nuevas columnas (FOR JOB IN).

También se pueden mostrar los mismos datos en formato XML, usando "PIVOT XML" y cambiando el contenido de la expresión FOR JOB IN por ANY (o una subconsulta), en lugar de las nuevas columnas.

select *
  from  (select to_char(HIREDATE,'yyyy') anio, JOB from emp)
pivot xml (count(*) for job in (ANY));

ANIO
----
JOB_XML
----------------------------------------------------------------------------------------
1980
<PivotSet><item><column name = "JOB">CLERK</column><column name = "COUNT(*)">2
</column></item></PivotSet>

1981
<PivotSet><item><column name = "JOB">ANALYST</column>&<column name = "COUNT(*)">1
</column></item><item><column name = "JOB
">CLERK</column><column name = "COUNT(*)">1
</column></item><item><column
name = "JOB">MANAGER</column><column name = "COUNT(*)">2
</column></item><item><column name = "JOB">SALESMAN</column>
<column name = "COUNT(*)">4
</column></item></PivotSet>

1987
<PivotSet><item><column name = "JOB">ANALYST</column><column name = "COUNT(*)">1
</column></item><item><column name = "JOB
">CLERK</column><column name = "COUNT(*)">1
</column></item></PivotSet>

Y ahora vemos los ejemplos con UNPIVOT. Para ello creamos una vista, a partir de la consulta anterior del ejemplo:

create or replace
  view emp_pivot as
  select anio, ANALYST, CLERK, MANAGER, SALESMAN
    from  (select to_char(HIREDATE,'yyyy') anio, JOB from emp)
   pivot (count(*) for job in ('ANALYST' as ANALYST,
                               'CLERK' as CLERK, 'MANAGER' as MANAGER,
                               'SALESMAN' as SALESMAN));
View created.


select * from emp_pivot;

ANIO    ANALYST      CLERK    MANAGER    SALESMAN
---- ---------- ---------- ---------- ----------
1981          1      1        2           4
1980          0      2        0           0
1987          1      1        0           0

Y ahora vamos a convertir las columnas (analyst, clerk, etc), en filas de otra consulta, mostrando el nº de contratos por año y puesto:

select * from emp_pivot
      unpivot (contratos for puesto in
                (ANALYST as 'ANALISTA',
                 CLERK as 'SECRETARIA',
                 MANAGER as 'DIRECTOR',
                 SALESMAN as 'VENDEDOR'));

ANIO PUESTO     CONTRATOS
---- ---------- ----------
1981 ANALISTA         1
1981 SECRETARIA       1
1981 DIRECTOR         2
1981 VENDEDOR         4
1980 ANALISTA         0
1980 SECRETARIA       2
1980 DIRECTOR         0
1980 VENDEDOR         0
1987 ANALISTA         1
1987 SECRETARIA       1
1987 DIRECTOR         0
1987 VENDEDOR         0

En el Manual de Oracle tienes un ejemplo más complejo, con múltiples agregados.


Enlaces

Última modificación ( 02.06.2016 )
 
Área de Tecnologías de la Información y las Comunicaciones Aplicadas
Volver al incio del documento Volver al inicio del documento
Área de Tecnologías de la Información y las Comunicaciones Aplicadas