Ejemplos de PIVOT y UNPIVOT

PIVOT y UNPIVOT son parámetros de la cláusula FROM que alternan la salida de la consulta de filas a columnas y de columnas a filas, respectivamente. Representan resultados de consultas tabulares en un formato fácil de leer. En los siguientes ejemplos se utilizan datos y consultas de prueba para mostrar cómo utilizarlos.

Para obtener más información sobre estos parámetros y sus opciones predeterminadas, consulte Cláusula FROM.

Ejemplos de PIVOT

Configure la tabla y los datos de muestra y utilícelos para ejecutar las consultas de ejemplo posteriores.


CREATE TABLE part (
    partname varchar,
    manufacturer varchar,
    quality int,
    price decimal(12, 2)
);

INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);
INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);
INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);

INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);
INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);
INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);

INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);
INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);
INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);

PIVOT en partname con una agregación AVG en price.


SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
    AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);

La consulta genera el siguiente resultado.


  prop   |  rudder  |  wing
---------+----------+---------
 10.33   | 2.71     | 11.50

En el ejemplo anterior, los resultados se transforman en columnas. En el siguiente ejemplo se muestra una consulta de GROUP BY que devuelve los precios promedio en filas, en lugar de hacerlo en columnas.


SELECT partname, avg(price)
FROM (SELECT partname, price FROM part)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;

La consulta genera el siguiente resultado.


 partname |  avg
----------+-------
 prop     | 10.33
 rudder   |  2.71
 wing     | 11.50

Un ejemplo de PIVOT con manufacturer como columna implícita.


SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) FOR quality IN (1, 2, NULL)
);

La consulta genera el siguiente resultado.


 manufacturer      | 1  | 2  | null
-------------------+----+----+------
 local parts co    | 1  | 1  |  1
 big parts co      | 1  | 1  |  1
 small parts co    | 1  | 0  |  2

Columnas de tabla de entrada a las que no se hace referencia en la definición de PIVOT se agregan implícitamente a la tabla de resultados. Este es el caso de la columna manufacturer del ejemplo anterior. En el ejemplo también se muestra que NULL es un valor válido para el operador IN.

PIVOT en el ejemplo anterior devuelve información similar a la siguiente consulta, lo que incluye GROUP BY. La diferencia es que PIVOT devuelve el valor 0 para la columna 2 y el fabricante small parts co. La consulta GROUP BY no contiene una fila correspondiente. En la mayoría de los casos, PIVOT inserta NULL si una fila no tiene datos de entrada para una columna determinada. No obstante, el agregado de recuento no devuelve NULL y 0 es el valor predeterminado.


SELECT manufacturer, quality, count(*)
FROM (SELECT quality, manufacturer FROM part)
WHERE quality IN (1, 2) OR quality IS NULL
GROUP BY manufacturer, quality
ORDER BY manufacturer;

La consulta genera el siguiente resultado.


 manufacturer        | quality | count
---------------------+---------+-------
 big parts co        |         |     1
 big parts co        |       2 |     1
 big parts co        |       1 |     1
 local parts co      |       2 |     1
 local parts co      |       1 |     1
 local parts co      |         |     1
 small parts co      |       1 |     1
 small parts co      |         |     2

El operador PIVOT acepta alias opcionales en la expresión agregada y en cada valor para el operador IN. Utilice alias para personalizar los nombres de las columnas. Si no hay alias agregados, solo se utilizan alias de la lista IN. De lo contrario, el alias agregado se agrega al nombre de la columna con un guion bajo para separar los nombres.


SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na)
);

La consulta genera el siguiente resultado.


 manufacturer      | high_count  | low_count | na_count
-------------------+-------------+-----------+----------
 local parts co    |           1 |         1 |        1
 big parts co      |           1 |         1 |        1
 small parts co    |           1 |         0 |        2

Configure la tabla y los datos de muestra siguientes y utilícelos para ejecutar las consultas de ejemplo posteriores. Los datos representan las fechas de reserva de una colección de hoteles.


CREATE TABLE bookings (
    booking_id int,
    hotel_code char(8),
    booking_date date,
    price decimal(12, 2)
);

INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12);
INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54);

INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00);
INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00);
INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00);

INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00);
INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00);
INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00);

INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00);
INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00);
INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00);

INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00);
INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00);
INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00);

INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00);
INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00);
INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00);

INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00);
INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00);
INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00);

INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00);
INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00);
INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00);

INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00);
INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00);
INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00);

En este ejemplo de consulta, los registros de reserva se suman para obtener un total de cada semana. La fecha de finalización de cada semana se convierte en un nombre de columna.


SELECT * FROM
    (SELECT
       booking_id,
       (date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate,
       hotel_code AS "hotel code"
FROM bookings
) PIVOT (
    count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18') 
);

La consulta genera el siguiente resultado.


 hotel code | 2023-02-04  | 2023-02-11 | 2023-02-18
------------+-------------+------------+----------
 FOREST_L   |           3 |          2 |        1
 DESERT_S   |           4 |          3 |        2
 OCEAN_WV   |           3 |          3 |        3
 CITY_BLD   |           3 |          1 |        2

Amazon Redshift no admite CROSSTAB para dinamizar en varias columnas. Pero puede cambiar los datos de filas a columnas, de forma similar a una agregación con PIVOT, con una consulta como la siguiente. Para ello se utilizan los mismos datos de muestra de reservas que en el ejemplo anterior.


SELECT 
  booking_date,
  MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L,
  MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S,
  MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END)  AS OCEAN_WV
FROM bookings
GROUP BY booking_date
ORDER BY booking_date asc;

La consulta de ejemplo da como resultado las fechas de reserva que aparecen junto a frases cortas que indican qué hoteles están reservados.


 booking_date  | forest_l         | desert_s         | ocean_wv
---------------+------------------+------------------+--------------------
 2023-02-01    | forest is booked | desert is booked |  ocean is booked
 2023-02-02    | forest is booked | desert is booked |  ocean is booked
 2023-02-04    | forest is booked | desert is booked |  ocean is booked
 2023-02-05    |                  | desert is booked |        
 2023-02-06    |                  | desert is booked |

Las siguientes notas de uso se indican a continuación para PIVOT:

PIVOT se puede aplicar a tablas, subconsultas y expresiones de tabla comunes (CTE). PIVOT no se puede aplicar a ninguna expresión JOIN, CTE recursivos, expresiones PIVOT o UNPIVOT. Tampoco se admiten expresiones SUPER sin anidar ni tablas anidadas Redshift Spectrum.
PIVOT admite las funciones de agregación COUNT, SUM, MIN, MAX y AVG.
La expresión de agregación PIVOT tiene que ser una llamada de una función de agregación admitida. No se admiten expresiones complejas además de las de agregación. Los argumentos de agregación no pueden contener referencias a tablas distintas de las tablas PIVOT de entrada. Tampoco se admiten las referencias correlacionadas a una consulta principal. El argumento agregado puede contener subconsultas. Se pueden correlacionar internamente o en la tabla de entrada PIVOT.
Los valores de la lista PIVOT IN no pueden ser referencias de columna ni subconsultas. Cada valor debe ser compatible con el tipo de referencia de columna FOR.
Si los valores de la lista IN no tienen alias, PIVOT genera nombres de columna predeterminados. Para valores IN constantes como “abc” o 5, el nombre de columna predeterminado es la constante en sí. Para cualquier expresión compleja, el nombre de la columna es un nombre predeterminado estándar de Amazon Redshift, como ?column?.

Ejemplos de UNPIVOT

Configure los datos de muestra y utilícelos para ejecutar los ejemplos posteriores.


CREATE TABLE count_by_color (quality varchar, red int, green int, blue int);

INSERT INTO count_by_color VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);

UNPIVOT en las columnas de entrada roja, verde y azul.


SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

La consulta genera el siguiente resultado.


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |  23
 blue  |   7
 blue  |  40

De manera predeterminada, los valores NULL de la columna de entrada se omiten y no producen una fila de resultados.

En el siguiente ejemplo, se muestra UNPIVOT con INCLUDE NULLS.


SELECT *
FROM (
    SELECT red, green, blue
    FROM count_by_color
) UNPIVOT INCLUDE NULLS (
    cnt FOR color IN (red, green, blue)
);

Se genera el siguiente resultado.


 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |
 green |  23
 blue  |   7
 blue  |  40
 blue  |

Si se configura el parámetro INCLUDING NULLS, los valores de entrada NULL generan filas de resultados.

Un ejemplo de The following query shows UNPIVOT con quality como columna implícita.


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

La consulta genera el siguiente resultado.


 quality | color | cnt
---------+-------+-----
 high    | red   |  15
 normal  | red   |  35
 low     | red   |  10
 high    | green |  20
 low     | green |  23
 high    | blue  |   7
 normal  | blue  |  40

Columnas de tabla de entrada a las que no se hace referencia en la definición de UNPIVOT, se agregan implícitamente a la tabla de resultados. Este es el caso de la columna quality del ejemplo.

En el siguiente ejemplo, se muestra UNPIVOT con alias para valores en la lista IN.


SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red AS r, green AS g, blue AS b)
);

La consulta anterior genera el siguiente resultado.


 quality | color | cnt
---------+-------+-----
 high    | r     |  15
 normal  | r     |  35
 low     | r     |  10
 high    | g     |  20
 low     | g     |  23
 high    | b     |   7
 normal  | b     |  40

El operador UNPIVOT acepta alias opcionales en cada valor de la lista IN. Cada alias proporciona personalización de los datos de cada columna value.

Las siguientes notas de uso se indican a continuación para UNPIVOT.

UNPIVOT se puede aplicar a tablas, subconsultas y expresiones de tabla comunes (CTE). UNPIVOT no se puede aplicar a ninguna expresión JOIN, CTE recursivos, expresiones PIVOT o UNPIVOT. Tampoco se admiten expresiones SUPER sin anidar ni tablas anidadas Redshift Spectrum.
La lista UNPIVOT IN debe contener solo referencias de columna de tabla de entrada. Las columnas de la lista IN deben tener un tipo común con el que todas sean compatibles. La columna de valor UNPIVOT tiene este tipo común. La columna de nombre UNPIVOT es de tipo VARCHAR.
Si un valor de la lista IN no tiene un alias, UNPIVOT utiliza el nombre de la columna como valor predeterminado.

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Cláusula FROM

Ejemplos de JOIN