Convert one row into multiple rows with fewer columns

Question

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?

Answer 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;

Answer 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:

• SELECT DISTINCT on multiple columns

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).

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

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:

• Are PostgreSQL column names case-sensitive?

db<>fiddle here

Related with more examples and explanation:

• How to unpivot a table in PostgreSQL

Answer 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