Transponer columnas a filas en PostgreSQL

Para convertir en filas los valores almacenados en varias columnas de PostgreSQL, primero colóquelos en matrices y expándalas con unnest(). La función devuelve los elementos de la matriz como filas. crosstab() realiza el tipo de transformación opuesto: pivota filas categorizadas para convertirlas en columnas, por lo que no sirve para pasar de columnas a filas.

Por ejemplo, los cuatro valores de curso val1 a val4 pueden organizarse con un valor por fila y asociarse con el estudiante y el grado que ocupan la misma posición. Esto resulta útil cuando es necesario procesar o mostrar un registro ancho como una secuencia de pares atributo-valor. La consulta concreta depende de si los valores ya están en matrices o en columnas separadas de una tabla.

Transponer columnas a filas en PostgreSQL

Una tabla con una columna por atributo tiene una representación ancha. Para reorganizar los valores seleccionados, construya matrices en un orden coherente y expándalas juntas. La primera matriz puede contener las etiquetas y las demás, los valores correspondientes. Cada fila resultante contiene la etiqueta y los valores de esa posición.

La función unnest() de PostgreSQL expande matrices; no detecta ni transpone automáticamente todas las columnas de una tabla. En la consulta, usted elige las columnas y su orden. Así, la transformación queda explícita y se reduce el riesgo de emparejar por error valores de atributos distintos.

Crear la tabla de ejemplo

El siguiente SQL define la tabla de ejemplo e inserta los registros que se utilizan más adelante. Std_no admite valores nulos para ajustarse a los datos proporcionados: las instrucciones de inserción omiten esa columna, por lo que estas filas no tienen número de estudiante. StdName, StdDegree y CourseName contienen los cuatro pares de grado y valor de curso de dos estudiantes.

Use este esquema: CREATE TABLE Student (Std_no int, StdName text, StdDegree text, CourseName text);

Inserte las filas de ejemplo:

INSERT INTO Student (StdName, StdDegree, CourseName) VALUES ('test1','att1','val1'), ('test1','att2','val2'), ('test1','att3','val3'), ('test1','att4','val4'), ('test2','att1','val5'), ('test2','att2','val6'), ('test2','att3','val7'), ('test2','att4','val8');

Las filas están agrupadas por StdName; para test1, la correspondencia entre grados y cursos va de att1 con val1 a att4 con val4. Mantenga ese orden al construir matrices paralelas para que cada etiqueta siga emparejada con el valor previsto.

Usar crosstab() para pivotar filas y convertirlas en columnas

crosstab() resulta útil cuando la entrada consta de filas con un identificador de fila, una categoría y un valor, y el resultado deseado tiene una columna por categoría. Se trata de un pivote de filas a columnas, la dirección opuesta a la conversión de columnas en filas. Se incluye aquí para distinguir esta tarea de reorganización, cuyo nombre es similar.

La función la proporciona la extensión tablefunc de PostgreSQL. Una configuración habitual es CREATE EXTENSION IF NOT EXISTS tablefunc;. La consulta de origen debe devolver un nombre de fila, una categoría y un valor; además, hay que declarar el tipo del registro de salida. Por ejemplo, la forma crosstab(source_sql, category_sql) utiliza una consulta para los datos y otra para definir el orden de las categorías. Las columnas de salida deben coincidir con la estructura de resultado declarada; crosstab() no infiere un número variable de columnas para un resultado de consulta con estructura fija.

Use crosstab() cuando quiera convertir categorías en columnas de un informe. No la use para la conversión de columnas a filas que se explica en este artículo: su propósito y la dirección de su resultado son distintos.

Usar unnest() para convertir matrices en filas

unnest(anyarray) expande una matriz y devuelve un conjunto de filas. Para convertir varias matrices paralelas en columnas alineadas, use la forma con varias matrices en la cláusula FROM y asigne alias a las columnas resultantes. A continuación se muestra una ilustración en Python de la expansión por posición; es un ejemplo independiente, no una consulta de PostgreSQL:

El código y las etiquetas o salidas visibles de esta sección se mantienen en inglés.

labels = ["StdName", "StdDegree", "CourseName"]
values = ["test1", "att1", "val1"]
for label, value in zip(labels, values):
    print(f"{label}: {value}")

StdName: test1
StdDegree: att1
CourseName: val1

Para expandir columnas de la tabla de ejemplo, seleccione una fila de origen y pase sus campos junto con una matriz de sus etiquetas. Esta consulta de PostgreSQL selecciona la fila test1 cuyo grado es att1 y devuelve un par atributo-valor por cada columna seleccionada:

SELECT t.attribute, t.value FROM Student AS s CROSS JOIN LATERAL unnest(ARRAY['StdName','StdDegree','CourseName']::text[], ARRAY[s.StdName, s.StdDegree, s.CourseName]::text[]) AS t(attribute, value) WHERE s.StdName = 'test1' AND s.StdDegree = 'att1';

El resultado tiene dos columnas, attribute y value, y tres filas: StdName con test1, StdDegree con att1 y CourseName con val1. Cada valor procede de la misma fila de la tabla de ejemplo, así que no se añade ningún número de estudiante. La consulta de PostgreSQL se presenta como SQL y no se ejecutó en el entorno de ejecución de Python proporcionado.

Las dos matrices contienen elementos de texto compatibles: una contiene las etiquetas y la otra, los valores de las columnas seleccionadas. La forma de varias matrices de unnest() de PostgreSQL rellena las matrices más cortas con valores NULL hasta alcanzar la longitud de la más larga. Si cada posición debe representar un registro completo, asegúrese de que las matrices tengan la misma longitud; de lo contrario, gestione esos valores nulos de forma deliberada.

Otra forma consiste en incluir llamadas a unnest() en la lista SELECT. Para una sola matriz, SELECT unnest(ARRAY['att1','att2','att3','att4']) AS degree; devuelve una fila de resultado por elemento. Es más difícil razonar sobre varias funciones que devuelven conjuntos en una lista SELECT, y su comportamiento depende de la versión; si necesita columnas alineadas, use la forma con varias matrices en FROM. Esta expresa explícitamente la relación entre las matrices y asigna nombres claros a las columnas de salida.

El ejemplo es deliberadamente pequeño. Para reorganizar valores de una tabla real, seleccione las columnas necesarias en matrices siguiendo un orden definido o utilice una consulta lateral cuando cada fila de origen tenga sus propios valores de matriz. unnest() expande matrices; no convierte automáticamente columnas arbitrarias de una tabla en un conjunto normalizado de filas atributo-valor.

Elegir el método adecuado

Use unnest() cuando tenga matrices cuyos elementos deban convertirse en filas, incluidas las matrices paralelas que deban aparecer como columnas de salida alineadas. Mantenga coherentes sus longitudes y el orden de sus elementos, y asigne nombres a las columnas de salida mediante una lista de alias. Si el origen es una tabla normal con columnas escalares no relacionadas, especifique qué campos quiere agrupar en matrices o cree una consulta que se ajuste a la estructura de esa tabla.

Use crosstab() cuando quiera convertir filas de entrada categorizadas en columnas de salida. La dirección es la opuesta: crosstab() pivota filas y las convierte en columnas, mientras que unnest() expande elementos de matrices y los convierte en filas. Elegir según la forma deseada del resultado evita aplicar una función de pivote a la tarea equivocada.