how to get separate value from same column on Oracle SQL

apartment_Table

From the table, WK code is road_name and CO code is apartment number.

update_Date is the latest data when user update in the system. I would like to know How do I get result like below

(latest update_Date WK belong to latest update_Date CO)

SQL I wrote:

SELECT
View_B_ID AS ViewID,
DECODE(P.address1, NULL, 'Street Not Available, ', SUBSTR(P.address1, 1, 50) || ', ')
|| DECODE(P.address2, NULL, '', SUBSTR(P.address2, 1, 50) || ', ')
|| DECODE(P.CITY, NULL, '', P.CITY || ', ')
|| DECODE(P.STAT_CODE, NULL, '', STAT_CODE || ', ')
|| DECODE(P.ZIP, NULL, '', P.ZIP) AS tenant_address
FROM View_B --assume I get data from View_B
 LEFT JOIN (SELECT R.ID,
            R.address1,
            R.address2,
            R.CITY,
            R.STAT_CODE,
            R.ZIP,
            ROW_NUMBER() OVER(PARTITION BY R.ID ORDER BY R.update_Date DESC) AS RN
            FROM apartment_Table R
            WHERE R.code = 'CO'
            ) P
ON ViewID = P.ID
AND P.RN = 1

Output:

Should I use LISTAGG function ? how to get the correct result ?
please help. thank you so much

You could use max keep dense_rank aggregate function to get the job done as below :

SELECT ID,
       REGEXP_REPLACE(MAX(ADDRESS1) /* WK code road_name */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ADDRESS1) /* CO code apartment number */
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'CO', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ADDRESS2)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(CITY)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(STATE_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC) || ', ' || 
                      MAX(ZIP_CODE)
                         KEEP(DENSE_RANK FIRST ORDER BY
                           DECODE(CODE, 'WK', 1, 2) ASC,
                           UPDATE_DATE DESC)
                 , '(,[[:space:]])(,)'
                 , '\2'
              ) AS TENANT_ADDRESS
  FROM YOUR_TABLE_NAME T
 GROUP BY ID;

demo on db<>fiddle

See oracle documentation for more details

You could create a query (tmax) with max dates per ID for both codes 'WK' and 'CO' having them in the same row. Then join your table twice by codes and just concat your tenant's latest address:

SELECT      tmax.ID, 
            twk.ADDRESS1 || ', ' || tco.ADDRESS1 || ', ' || tco.CITY || ', ' || tco.STATE_CODE || ', ' || tco.ZIP_CODE "TENANT_ADDRESS"
FROM        ( Select      ID, 
                          MAX(CASE WHEN CODE = 'WK' THEN UPD_DATE END) "WK_DATE",
                          MAX(CASE WHEN CODE = 'CO' THEN UPD_DATE END) "CO_DATE" 
              From        tbl
              Group By    ID
            ) tmax
INNER JOIN  tbl twk ON(twk.ID = tmax.ID And twk.UPD_DATE = tmax.WK_DATE and twk.CODE = 'WK')
INNER JOIN  tbl tco ON(tco.ID = tmax.ID And tco.UPD_DATE = tmax.CO_DATE and tco.CODE = 'CO')

With your sample data:

WITH
	tbl (ID,	UPD_DATE,	CODE,	ADDRESS1,	ADDRESS2,	CITY,	STATE_CODE,	ZIP_CODE) AS
		(
			Select 1, To_Date('2023/02/15', 'yyyy/mm/dd'), 'CO', 'Apt 320', 	Null, 'NYC', 'NY', 10012 From Dual Union All 
			Select 1, To_Date('2021/12/03', 'yyyy/mm/dd'), 'CO', 'Apt 105', 	Null, 'NYC', 'NY', 10012 From Dual Union All
			Select 1, To_Date('2023/02/15', 'yyyy/mm/dd'), 'WK', '5th Avenue', 	Null, 'NYC', 'NY', 10012 From Dual Union All
			Select 2, To_Date('2023/02/15', 'yyyy/mm/dd'), 'CO', 'Apt 325', 	Null, 'NYC', 'NY', 10012 From Dual Union All
			Select 2, To_Date('2022/01/12', 'yyyy/mm/dd'), 'CO', 'Apt 123', 	Null, 'NYC', 'NY', 10012 From Dual Union All
			Select 2, To_Date('2023/02/14', 'yyyy/mm/dd'), 'WK', '4th Avenue', 	Null, 'NYC', 'NY', 10012 From Dual Union All
			Select 2, To_Date('2021/02/11', 'yyyy/mm/dd'), 'WK', '5th Avenue', 	Null, 'NYC', 'NY', 10012 From Dual 
		)

... you should get this as the result

        ID TENANT_ADDRESS                        
---------- --------------------------------------
         1 5th Avenue, Apt 320, NYC, NY, 10012   
         2 4th Avenue, Apt 325, NYC, NY, 10012