PostgreSQL convierte columnas en filas? ¿Transponer?

Esta es la respuesta más completa que te podemos brindar, pero primero estúdiala detenidamente y analiza si se adapta a tu trabajo.

Solución:

Basando mi respuesta en una tabla de la forma:

CREATE TABLE tbl (
   sl_no int
 , username text
 , designation text
 , salary int
);

Cada fila da como resultado una nueva columna para devolver. Con un tipo de retorno dinámico como este, es casi imposible hacerlo completamente dinámico con una sola llamada a la base de datos. Demostrar soluciones con dos pasos:

1. Generar consulta
2. Ejecutar consulta generada

Generalmente, esto está limitado por el número máximo de columnas que puede contener una tabla. Por tanto, no es una opción para tablas con más de 1600 filas (o menos). Detalles:

• ¿Cuál es el número máximo de columnas en una consulta de selección de PostgreSQL?

Postgres 9.3 o anterior

Solución dinámica con crosstab()

• Completamente dinámico, funciona para cualquier mesa. Proporcione el nombre de la tabla en dos lugares:

SELECT 'SELECT *
FROM   crosstab(
       ''SELECT unnest(''' || quote_literal(array_agg(attname))
                           || '''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || ']) AS val
        FROM   ' || attrelid::regclass || '
        ORDER  BY generate_series(1,' || count(*) || '), 2''
   ) t (col text, '
     || (SELECT string_agg('r'|| rn ||' text', ',')
         FROM (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

Podría estar envuelto en una función con un solo parámetro …
Genera una consulta de la forma:

SELECT *
FROM   crosstab(
       'SELECT unnest(''sl_no,username,designation,salary''::text[]) AS col
             , row_number() OVER ()
             , unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text]) AS val
        FROM   tbl
        ORDER  BY generate_series(1,4), 2'
   ) t (col text, r1 text,r2 text,r3 text,r4 text)

Produce el resultado deseado:

col         r1    r2      r3     r4
-----------------------------------
sl_no       1      2      3      4
username    A      B      C      D
designation XYZ    RTS    QWE    HGD
salary      10000  50000  20000  34343

Solución simple con unnest()

SELECT 'SELECT unnest(''sl_no, username, designation, salary''::text[] AS col)
     , ' || string_agg('unnest('
                    || quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
                    || '::text[]) AS row' || sl_no, E'n     , ') AS sql
FROM   tbl;

• Lento para tablas con más de un par de columnas.

Genera una consulta de la forma:

SELECT unnest('sl_no, username, designation, salary'::text[]) AS col
     , unnest('10,Joe,Music,1234'::text[]) AS row1
     , unnest('11,Bob,Movie,2345'::text[]) AS row2
     , unnest('12,Dave,Theatre,2356'::text[]) AS row3
     , unnest('4,D,HGD,34343'::text[]) AS row4

Mismo resultado.

Postgres 9.4+

Solución dinámica con crosstab()

Use esto si puede. Supera al resto.

SELECT 'SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM '
                              || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || '])
                 WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, '
     || (SELECT string_agg('r'|| rn ||' text', ', ')
         FROM  (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

Operando con attnum en lugar de los nombres de columna reales. Más sencillo y rápido. Une el resultado a pg_attribute una vez más o integrar nombres de columna como en el ejemplo de la pág. 9.3.
Genera una consulta de la forma:

SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM tbl) t
             , unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text])
                WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, r1 text, r2 text, r3 text, r4 text);

Esto utiliza una amplia gama de funciones avanzadas. Demasiado para explicar.

Solución simple con unnest()

Uno unnest() ahora puede tomar varias matrices para desanidar en paralelo.

SELECT 'SELECT * FROM unnest(
  ''sl_no, username, designation, salary''::text[]
, ' || string_agg(quote_literal(ARRAY[sl_no::text, username::text, designation::text, salary::text])
              || '::text[]', E'n, ')
    || E') n AS t(col,' || string_agg('row' || sl_no, ',') || ')' AS sql
FROM   tbl;

Resultado:

SELECT * FROM unnest(
 'sl_no, username, designation, salary'::text[]
,'10,Joe,Music,1234'::text[]
,'11,Bob,Movie,2345'::text[]
,'12,Dave,Theatre,2356'::text[])
 AS t(col,row1,row2,row3,row4)

Violín SQL ejecutándose en la pág. 9.3.

SELECT
   unnest(array['Sl.no', 'username', 'Designation','salary']) AS "Columns",
   unnest(array[Sl.no, username, value3Count,salary]) AS "Values"
FROM view_name
ORDER BY "Columns"

Referencia: convertingColumnsToRows

Si (como yo) necesitabas esta información de un script bash, ten en cuenta que hay un simple interruptor de línea de comandos para psql para indicarle que genere columnas de la tabla como filas:

psql mydbname -x -A -F= -c "SELECT * FROM foo WHERE id=123"

los -x la opción es la key para hacer que psql genere columnas como filas.

Reseñas y puntuaciones del tutorial

Al final de la web puedes encontrar las interpretaciones de otros usuarios, tú aún tienes la opción de insertar el tuyo si te gusta.

---

---

Utiliza Nuestro Buscador