'Convert one row into multiple rows with fewer columns

I'd like to convert single rows into multiple rows in PostgreSQL, where some of the columns are removed. Here's an example of the current output:

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

Using the following query:

SELECT
   name, st, ot, dt
FROM
   times;

And here's what I want:

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  |

How can I modify the query to get the above desired output?

sqlpostgresqlunpivotlateral


Solution 1:[1]

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;

Solution 2:[2]

The core problem is the reverse of a pivot / crosstab operation. Sometimes called "unpivot".

Basically, Abelisto's query is the way to go in Postgres 9.3 or later. Related:

You may want to use LEFT JOIN LATERAL ... ON u.val <> 0 to include names without valid values in the result (and shorten the syntax a bit).

If you have more than a few value columns (or varying lists of columns) you may want to use a function to build and execute the query automatically:

CREATE OR REPLACE FUNCTION f_unpivot_columns(VARIADIC _cols text[])
  RETURNS TABLE(name text, t text, val int)
  LANGUAGE plpgsql 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$;

Call:

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

Or:

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

Columns names are provided as string literals and must be in correct (case-sensitive!) spelling with no extra double-quotes. See:

db<>fiddle here

Related with more examples and explanation:

Solution 3:[3]

One way:

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3

Related Questions