pasar - PostgreSQL convertir columnas en filas? ¿Transponer?

transponer filas en columnas mysql

Question

Tengo una función PostgreSQL (o tabla) que me da el siguiente resultado:

Sl.no    username    Designation    salary   etc..
 1        A           XYZ            10000    ...
 2        B           RTS            50000    ...
 3        C           QWE            20000    ...
 4        D           HGD            34343    ...

Ahora quiero la salida de la siguiente manera:

Sl.no            1       2        3       4       ...
 Username        A       B        C       D       ...
 Designation     XYZ     RTS      QWE     HGD     ...
 Salary          10000   50000    20000   34343   ...

¿Como hacer esto?

Answer 1

Basando mi respuesta en una tabla del formulario:

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

En general, esto está limitado por el número máximo de columnas que puede contener una tabla. Entonces, 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 mayor

Solución dinámica con crosstab()

• Completamente dinámico, funciona para cualquier mesa. Proporcione el nombre de la mesa 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 ser 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()

Usa esto si puedes. 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 lugar de nombres de columnas reales. Más simple y más rápido. Unir el resultado a pg_attribute una vez más o integrar nombres de columna como en el ejemplo de pg 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 toda una gama de características avanzadas. Demasiado para explicar.

Solución simple con unnest()

Un unnest() ahora puede tomar múltiples arreglos para unnt 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)

SQL Fiddle se ejecuta en la página 9.3.

Answer 2

No hay una forma adecuada de hacer esto en SQL simple o PL / pgSQL.

Será mucho mejor hacer esto en la aplicación, que obtiene los datos de la base de datos.

Answer 3

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

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

La opción -x es la clave para que psql genere columnas como filas.

Answer 4

Tengo un enfoque más simple que el que Erwin señaló anteriormente, ese trabajador para mí con Postgres (y creo que debería funcionar con todas las principales bases de datos relacionales cuyo soporte es el estándar SQL)

Puede utilizar simplemente UNION en lugar de tabla de referencias cruzadas:

SELECT text ''a'' AS "text" UNION SELECT ''b'';

 text
------
 a
 b
(2 rows)

Por supuesto que eso depende del caso en el que vayas a aplicar esto. Teniendo en cuenta que sabe de antemano qué campos necesita, puede adoptar este enfoque incluso para consultar diferentes tablas. Es decir:

SELECT ''My first metric'' as name, count(*) as total from first_table UNION
SELECT ''My second metric'' as name, count(*) as total from second_table 

 name             | Total
------------------|--------
 My first metric  |     10
 My second metric |     20
(2 rows)

Es un enfoque más mantenible, en mi humilde opinión. Mire esta página para obtener más información: https://www.postgresql.org/docs/current/typeconv-union-case.html

Answer 5

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