funcion - transponer filas en columnas mysql

Convierta una fila en varias filas con menos columnas

Question

Me gustaría convertir filas individuales en varias filas en PostgreSQL, donde se eliminan algunas de las columnas. Aquí hay un ejemplo de la salida actual:

name | st | ot | dt |
-----|----|----|----|
Fred | 8  | 2  | 3  |
Jane | 8  | 1  | 0  |
Samm | 8  | 0  | 6  |  
Alex | 8  | 0  | 0  |  

Usando la siguiente consulta:

SELECT
   name, st, ot, dt
FROM
   times;

Y esto es lo que quiero:

name |  t | val |
-----|----|-----|
Fred | st |  8  |
Fred | ot |  2  |
Fred | dt |  3  |
Jane | st |  8  |
Jane | ot |  1  |
Samm | st |  8  |
Samm | dt |  6  |
Alex | st |  8  |

¿Cómo puedo modificar la consulta para obtener el resultado deseado?

Answer 1

De una sola mano:

with times(name , st , ot , dt) as(
select ''Fred'',8  , 2  , 3  union all
select ''Jane'',8  , 1  , 0  union all
select ''Samm'',8  , 0  , 6  union all
select ''Alex'',8  , 0  , 0  
)

select name, key as t, value::int  from 
(
    select name, json_build_object(''st'' ,st , ''ot'',ot, ''dt'',dt) as j
    from times
) t
join lateral json_each_text(j)
on true
where value <> ''0''
-- order by name, case when key = ''st'' then 0 when key = ''ot'' then 1 when key = ''dt'' then 2 end

Answer 2

SELECT
  times.name, x.t, x.val
FROM
  times cross join lateral (values(''st'',st),(''ot'',ot),(''dt'',dt)) as x(t,val)
WHERE
  x.val <> 0;

Answer 3

El problema principal es el reverso de una operación de pivote / cruce . A veces se llama "unpivot" .

Básicamente, la consulta de Abelisto es el camino a seguir en Postgres 9.3 o posterior. Relacionado:

• SELECCIONE DISTINCT en múltiples columnas

Es posible que desee utilizar LEFT JOIN LATERAL ... ON u.val <> 0 para incluir nombres sin valores válidos en el resultado (y acortar un poco la sintaxis).

• ¿Cuál es la diferencia entre LATERAL y una subconsulta en PostgreSQL?

Si tiene más de unas pocas columnas de valores (o listas de columnas variables), puede usar una función para compilar y ejecutar la consulta automáticamente:

CREATE OR REPLACE FUNCTION f_unpivot_columns(VARIADIC _cols text[])
  RETURNS TABLE(name text, t text, val int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT
     ''SELECT t.name, u.t, u.val
      FROM   times t
      LEFT   JOIN LATERAL (VALUES ''
          || string_agg(format(''(%L, t.%I)'', c, c), '', '')
          || '') u(t, val) ON (u.val <> 0)''
   FROM   unnest(_cols) c
   );
END
$func$  LANGUAGE plpgsql;

Llamada:

SELECT * FROM f_unpivot_times_columns(VARIADIC ''{st, ot, dt}'');

O:

SELECT * FROM f_unpivot_columns(''ot'', ''dt'');

Los nombres de las columnas se proporcionan como literales de cadena y deben estar en ortografía correcta (¡distinga entre mayúsculas y minúsculas!) Sin comillas dobles adicionales. Ver:

• ¿Los nombres de las columnas PostgreSQL distinguen entre mayúsculas y minúsculas?

dbfiddle aquí

Relacionado con más ejemplos y explicación:

• Cómo desvincular una tabla en PostgreSQL