Transponer columnas a filas en PostgreSQL

1. Transponer columnas a filas
2. Crear una tabla en PostgreSQL
3. Use la función crosstab() para transponer columnas a filas en PostgreSQL
4. Utilice la función unnest() para transponer columnas a filas en PostgreSQL
5. Conclusión

Este artículo se ha articulado especialmente para analizar un método que utiliza las columnas proporcionadas en la entrada para transformarlas en filas.

Hay varios casos en los que los datos deben mostrarse en un formato diferente. Este artículo describe cómo transformar los datos de columnas regulares a filas.

Para resumir todo el artículo, el problema que requiere una solución se ha discutido en una sección. Luego, se introdujeron los dos métodos, crosstab() y unnest(), para ayudar a resolver el problema.

Por último, se analizan ejemplos de cada método para proporcionar una mejor comprensión de ambos métodos.

Transponer columnas a filas

El problema que se discute en este artículo es cómo transponer columnas a filas. Por ejemplo, tienes la siguiente tabla:

Std_no.	Name	Degree
  1		 A		  W
  2 	 B        X
  3  	 C        Y
  4  	 D        Z

Este artículo describe cómo convertir las columnas de la tabla en filas. Por lo tanto, la solución esperada debería ser:

Std_no.		1		2		3		4
Name		A		B		C		D
Degree		W		X		Y		Z

El artículo menciona el procedimiento para convertir las columnas en filas. La entrada puede tener la forma de columnas de tabla o matrices.

Ambas posibilidades se abordan en el artículo.

Crear una tabla en PostgreSQL

Se usarán la misma tabla y los mismos valores para facilitar la comprensión a lo largo del artículo. Esto cambiará el enfoque hacia la comprensión de las funciones en lugar de dedicar tiempo a los diferentes esquemas.

La siguiente consulta se utiliza para definir una tabla:

CREATE TABLE Student(Std_no int, StdName TEXT, StdDegree TEXT, CourseName TEXT);

Después de la creación exitosa de una tabla, se insertan ocho registros en ella. Los registros son los siguientes:

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');

Una vez que se crea la tabla y se agregan los registros, se puede usar la declaración SELECT para ver la condición actual de la tabla.

Select * from Student;

La instantánea de la tabla es la siguiente:

Ahora podemos pasar a la definición y el uso de las dos funciones que ayudan a transponer columnas a filas en PostgreSQL.

Use la función crosstab() para transponer columnas a filas en PostgreSQL

Analicemos qué es la función crosstab().

Esta función es compatible con PostgreSQL versión 9.7 y superior. Se puede utilizar de varias maneras con diferentes argumentos.

La sintaxis de la función crosstab() es la siguiente:

1. tabla de referencias cruzadas (texto sql)
2. crosstabN(texto sql)
3. tab cruzado (texto source_sql, texto category_sql)
4. tab cruzado (texto sql, N entero)

El funcionamiento y la descripción de cada argumento se explican en detalle en la documentación de PostgreSQL.

Para este artículo, solo requerimos crosstab(sql text). El argumento en crosstab(sql text) es una consulta SQL.

Cómo funciona la función crosstab()

Esta sección se centra en el funcionamiento de la función crosstab(). Se utiliza un ejemplo para entender el concepto de la función crosstab().

Aquí hay una consulta que ha sido escrita con la ayuda de la función crosstab():

SELECT *
FROM crosstab(
  'select StdName, StdDegree, CourseName
   from Student
   where StdDegree = 'att2'
   or StdDegree = 'att3'
   order by 1,2')
AS Student(row_name text, category_1 text, category_2 text, category_3 text);

Esto es más fácil de entender con el concepto de consultas anidadas. La consulta entre comillas es la consulta interna que también funciona como argumento para la función crosstab(); se ejecuta al principio.

La consulta en el argumento devuelve registros donde el StdDegree tiene el valor att2 o att3. Esto se muestra mediante la función crosstab() con los encabezados row_name, category_1, category_2 y category_3.

El resultado del ejemplo es el siguiente:

Utilice la función unnest() para transponer columnas a filas en PostgreSQL

Discutamos ahora sobre la función unnest(). La función unnest() es una función de matriz compatible con la versión 9.7 y superior del servidor PostgreSQL.

Hay muchas otras funciones de matriz que se ofrecen en PostgreSQL que se mencionan en la documentación del servidor.

La sintaxis de la función unnest() es la siguiente:

unnest(anyarray)

La función unnest() toma una matriz, o un conjunto de matrices, como argumento. Expande la matriz en registros separados y muestra el resultado al usuario.

La función unnest() admite matrices simples y multidimensionales.

Cómo funciona la función unnest()

Esta sección se centra en el funcionamiento de la función unnest(). Se utiliza un ejemplo para entender el concepto de la función unnest().

La función unnest() se puede utilizar de dos formas diferentes. El ejemplo 1 menciona la primera forma en que se puede usar unnest() para transponer columnas a filas.

SELECT unnest('{Std_no, StdName, StdDegree, CourseName}'::text[]) AS col
	, unnest('{1,test1,att1,val1}'::text[]) AS row1
	, unnest('{2,test1,att2,val2}'::text[]) AS row2
	, unnest('{3,test1,att3,val3}'::text[]) AS row3
	, unnest('{4,test1,att4,val4}'::text[]) AS row4;

La consulta utiliza la función unnest() que toma una matriz como argumento y devuelve registros separados. Por lo tanto, el resultado de la consulta transpone columnas a filas:

La consulta mencionada en el Ejemplo 1 se puede escribir en un formato ligeramente diferente. Ambos ejemplos tienen consultas similares que realizan la misma tarea de convertir columnas en filas.

Aquí hay un formato diferente para la consulta mencionada en el ejemplo 1:

SELECT * FROM unnest
    (
     '{Std_no, StdName, StdDegree, CourseName}'::text[]
     ,'{1,test1,att1,val1}'::text[]
     ,'{2,test1,att2,val2}'::text[]
     ,'{3,test1,att3,val3}'::text[]
     ,'{4,test1,att4,val4}'::text[]
	)
 AS t(col,row1,row2,row3,row4);

Se puede ver la diferencia entre los dos ejemplos. En la primera consulta mencionada en el Ejemplo 1, cada función unnest() se ocupaba de una matriz de una sola dimensión.

Cada función unnest() tiene un encabezado separado en el ejemplo 1.

En este ejemplo, solo se ha utilizado una función unnest() que se adapta a una matriz multidimensional. Los encabezados de cada una de las nuevas columnas se mencionan al final.

La consulta genera el mismo resultado que el Ejemplo 1:

Conclusión

Se pueden probar diferentes funciones y declaraciones SQL para transponer columnas a filas. Cuando se transpone, cada fila se transforma en una nueva columna.

Este resultado dinámico dificulta que una sola consulta transponga columnas en filas. Por lo tanto, las funciones crosstab() y unnest() se utilizan para ayudar con tales problemas.