Filter varchar col from another col value in pivot

Question

I have a table like below:

object_key	created_by	updated_on	updated_by	attr_type	value_name	value_num
1	user1	3/21/2021	user1	name	John
1	user4	4/15/2021	user3	mobile_number		4567865
1	user3	4/21/2021	user2	office_number		2378783

I'm trying to pivot using the query:

SELECT object_key,
    max(created_on) AS created_on,
    max(updated_on) AS updated_on,
    max(updated_by) FILTER (WHERE updated_on = max(updated_on)) AS updated_by,
    max(array_to_string(value_name, '||'::text)) FILTER (WHERE attr_type = 'name' )as name,
    max(array_to_string(value_num, '||'::text)) FILTER (WHERE attr_type = 'mobile_number') AS mobile_number,
    max(array_to_string(value_num, '||'::text)) FILTER (WHERE attr_type = 'office_number') AS office_number
FROM object_attributes   
GROUP BY object_key;

Pivoted table must be like:

object_key	created_by	updated_on	updated_by	name	mobile_number	office_number
1	user1	4/21/2021	user2	John	4567865	2378783

updated_by should come from a row having highest updated_on date for each object_key.

But FILTER (WHERE updated_on = max(updated_on)) is not working in the query.

Is there a way to select updated_by using the updated_on in the pivot?

Is there a possibility of multiple values of the same *object_key* and *attr_type*? — Daniel Gimenez, Apr 20 '21 at 19:56
there is unique key on combined columns (object_key and attr_type) — Hari, Apr 21 '21 at 10:00
The displayed table disagrees with the one disclosed in your fiddle, where `value_name` and `value_num` are array columns (among other things). Please remember to disclose *actual* table definitions (`CREATE TABLE` statements) with every question. — Erwin Brandstetter, Apr 21 '21 at 16:16

Erwin Brandstetter · Accepted Answer · 2021-04-21T16:10:04.777

1

Can be achieved with a single SELECT combining window functions with DISTINCT ON:

Adapted to the table definition finally disclosed in your fiddle:

SELECT DISTINCT ON (object_key)
       object_key
     , first_value(created_by) OVER w AS created_by
     , updated_on
     , updated_by
     , min(array_to_string(value_name, '||')) FILTER (WHERE attr_type = 'Name'         ) OVER w AS "Name"
     , min(array_to_string(value_num , '||')) FILTER (WHERE attr_type = 'Mobile_number') OVER w AS "Mobile_number"
     , min(array_to_string(value_num , '||')) FILTER (WHERE attr_type = 'Office_number') OVER w AS "Office_number"
--   , min(array_to_string(value_name, '||')) FILTER (WHERE attr_type = 'status'       ) OVER w AS status  -- ??
FROM   object_attributes
WHERE  object_key IN (1,2)
WINDOW w AS (PARTITION BY object_key ORDER BY updated_on)
ORDER  BY object_key, updated_on DESC NULLS LAST;

db<>fiddle here

This works, because DISTINCT ON is applied after window functions. Consider the sequence of events in a SELECT query:

Best way to get result count before LIMIT was applied

We can use the FILTER clause in aggregate window functions. See:

The WINDOW clause is optional to shorten the code, you can spell it our 4x as well, same result.

Why you might need NULLS LAST:

Sort by column ASC, but NULL values first?

answered Apr 21 '21 at 00:12

Erwin Brandstetter

605,456
145
1,078
1,228

1

This should be accepted as this is the best answer. I have deleted mine. – Daniel Gimenez Apr 21 '21 at 14:50
@erwin-brandstetter Thanks for response. sry, I should have shared table structure first. I was getting error ``` function string_agg(integer[], unknown) does not exist``` . I checked your code. Updated the table creation script in db<>fiddle. please look into db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=0c71174e19ec62adfd38c6b5310ab093) your query also giving same error – Hari Apr 21 '21 at 15:45
@Hari: I updated accordingly. (That's why you should provide your table definition with every question.) – Erwin Brandstetter Apr 21 '21 at 16:04
@erwin-brandstetter: Thanks. I have added another field `created_on` in table. Pivot `created_on` must have least value of `created_on` for each object. so i added `first_value(created_on) over (PARTITION BY object_key order by created_on asc)` . now query has two `partitions by` with different order by (it may slow query perf). Can we re-write query in better way – Hari Apr 21 '21 at 18:42
Consider `last_value(created_on) over w` instead. – Erwin Brandstetter Apr 21 '21 at 18:49

eshirvana · Answer 2 · 2021-04-20T19:36:38.943

0

one way is to use window function first_value:

 SELECT 
    object_key,
    max(created_on) AS created_on,
    max(updated_on) AS updated_on,
    first_value(updated_by) over(order updated_on desc) AS updated_by,
    max(array_to_string(value_name,'||')) FILTER (WHERE attr_type = 'name' )as name,
    max(array_to_string(value_num, '||')) FILTER (WHERE attr_type = 'mobile_number') AS mobile_number,
    max(array_to_string(value_num, '||')) FILTER (WHERE attr_type = 'office_number') AS office_number
FROM object_attributes   
GROUP BY object_key;

edited Apr 20 '21 at 19:36

answered Apr 20 '21 at 19:30

eshirvana

23,227
3
22
38

ERROR: column "updated_by" must appear in the GROUP BY clause or be used in an aggregate function – Hari Apr 21 '21 at 09:56

Filter varchar col from another col value in pivot

2 Answers2