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?

Tetracycline

Do you want to include names in the result where all value columns are 0 - with one row holding NULL in t and val? – Audreyaudri

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;

Noctambulism

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

Audreyaudri

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

Exordium

Was about to suggest LATERAL example also, but you got first :) Still, you do not need to build any json object and parse it. Simple .. FROM times t, LATERAL ( VALUES ('st', t.st), ('ot', t.ot), ('dt', t.dt) ) c(t, val) does the trick, because you can reference to times table in LATERAL. – Enkindle

@ KristoMägi - Yeah, you're right. Just first thing appeared in my brain was using json, probably not most optimal way – Exordium

Another comment: when building static data-set with CTE then you do not need to SELECT .. UNION ALL, using VALUES is enough like that: ...AS ( VALUES ( 'Fred', 8, 2, 3 ), ( 'Jane', 8, 1, 0 ), ( 'Samm', 8, 0, 6 ), ( 'Alex', 8, 0, 0 ) ).. :) – Enkindle