pasar - PostgreSQL convertir columnas en filas? ¿Transponer?
transponer filas en columnas mysql (5)
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?
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 :
- Generar consulta
- 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:
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.
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.
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.
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
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