CODEWITHSUNDEEP
sqlpostgresqlunpivotlateral
mgig
mgig

Reputation: 2915

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?

Upvotes: 1

Views: 2607

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

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:

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42753

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

Upvotes: 0

Abelisto
Abelisto

Reputation: 15614

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;

Upvotes: 3

Related Questions