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
     , updated_on
     , updated_by
     , string_agg(value_name, ' || ') FILTER (WHERE attr_type = 'name'         ) OVER w AS name
     , string_agg(value_num , ' || ') FILTER (WHERE attr_type = 'mobile_number') OVER w AS mobile_number
     , string_agg(value_num , ' || ') FILTER (WHERE attr_type = 'office_number') OVER w AS office_number
     , string_agg(value_name , ' || ') FILTER (WHERE attr_type = 'status') OVER w AS status

FROM   object_attributes att 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;
ERROR:  function string_agg(text[], unknown) does not exist
LINE 6:      , string_agg(value_name, ' || ') FILTER (WHERE attr_typ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

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