Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE object_attributes (object_key int, created_by text, updated_on date, 
updated_by text, attr_type text, value_name text[], value_num int[]);

CREATE UNIQUE INDEX object_attributes_uniq
ON object_attributes(object_key, attr_type);

INSERT INTO object_attributes VALUES
  (1, 'user1', '2021-3-21', 'user1', 'Name'         , array['John Deb'], NULL)
, (1, 'user4', '2021-4-15', 'user3', 'Office_number', NULL  , array[4567865])
, (1, 'user7', '2021-4-20', 'user7', 'Mobile_number', NULL  , array[6666666,2378783])  -- added row to make use of string_agg visible
, (2, 'user3', '2021-4-21', 'user2', 'Office_number', NULL  , array[2378483])
, (2, 'user3', '2021-3-28', 'user1', 'Name'         , array['Robert K'], NULL)
, (2, 'user3', '2021-3-28', 'user1', 'Region'         , array['AP'], NULL)
;
6 rows affected

TABLE object_attributes;


object_key
created_by
updated_on
updated_by
attr_type
value_name
value_num


1
user1
2021-03-21
user1
Name
{"John Deb"}
null

1
user4
2021-04-15
user3
Office_number
null
{4567865}

1
user7
2021-04-20
user7
Mobile_number
null
{6666666,2378783}

2
user3
2021-04-21
user2
Office_number
null
{2378483}

2
user3
2021-03-28
user1
Name
{"Robert K"}
null

2
user3
2021-03-28
user1
Region
{AP}
null


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;


object_key
created_by
updated_on
updated_by
Name
Mobile_number
Office_number


1
user1
2021-04-20
user7
John Deb
6666666||2378783
4567865

2
user3
2021-04-21
user2
Robert K
null
2378483

object_key	created_by	updated_on	updated_by	attr_type	value_name	value_num
1	user1	2021-03-21	user1	Name	{"John Deb"}	null
1	user4	2021-04-15	user3	Office_number	null	{4567865}
1	user7	2021-04-20	user7	Mobile_number	null	{6666666,2378783}
2	user3	2021-04-21	user2	Office_number	null	{2378483}
2	user3	2021-03-28	user1	Name	{"Robert K"}	null
2	user3	2021-03-28	user1	Region	{AP}	null