Concatenating only desirable values with LISTAGG

In the Oracle DB there is a table with data "YEARS", see a table/image below

+-----+-----------+------------------+
| FID |    I0     |       BJA        |
+-----+-----------+------------------+
|   1 |     0     |       1949       |
|   2 |     0     |       1996       |
|   3 |     0     |       1970       |
|   4 |     1     |       1871       |  
|   4 |     0     |       1975       |
|   5 |     0     |       1967       |
|   6 |     0     |       1968       |
|   7 |     0     |       1926       |
|   7 |     1     |       2009       |
|   7 |     2     |       2012       |
|   7 |     3     |       2018       |
|   8 |     0     |       1956       |
|   9 |     0     |       1990       |
|  10 |     0     |       1953       |
|  10 |     1     |       1904       |
| ... |    ...    |       ...        |
+-----+-----------+------------------+

I tried to create a fiddle (here), but it did not work ...
CREATE TABLE YEARS (
"FID" NUMBER(10,0) NOT NULL,
"I0" NUMBER(10,0) NOT NULL,
"BJA" NUMBER(10,0)
);

INSERT ALL
INTO YEARS ("FID","I0","BJA") VALUES (1,0,1949)
INTO YEARS ("FID","I0","BJA") VALUES (2,0,1996)
INTO YEARS ("FID","I0","BJA") VALUES (3,0,1970)
INTO YEARS ("FID","I0","BJA") VALUES (4,1,1871)
INTO YEARS ("FID","I0","BJA") VALUES (4,0,1975)
INTO YEARS ("FID","I0","BJA") VALUES (5,0,1967)
INTO YEARS ("FID","I0","BJA") VALUES (6,0,1968)
INTO YEARS ("FID","I0","BJA") VALUES (7,0,1926)
INTO YEARS ("FID","I0","BJA") VALUES (7,1,2009)
INTO YEARS ("FID","I0","BJA") VALUES (7,2,2012)
INTO YEARS ("FID","I0","BJA") VALUES (7,3,2018)
INTO YEARS ("FID","I0","BJA") VALUES (8,0,1956)
INTO YEARS ("FID","I0","BJA") VALUES (9,0,1990)
INTO YEARS ("FID","I0","BJA") VALUES (10,0,1953)
INTO YEARS ("FID","I0","BJA") VALUES (10,1,1904)

SELECT 1 FROM dual;


I am trying to execute the following query:
`SELECT YEARS.FID,
MIN(YEARS.BJA) AS "CONSTRYEAR",
LISTAGG(YEARS.BJA, ', ')

You can use a window MIN() to find the construction year per FID and return it alongside all the details, like this:

SELECT
  YEARS.*
, MIN(BJA) OVER (PARTITION BY FID) AS CONSTRYEAR
FROM
  YEARS

It would give you an output like this:

FID
I0
BJA
CONSTRYEAR

1
0
1949
1949

2
0
1996
1996

3
0
1970
1970

4
1
1871
1871

4
0
1975
1871

5
0
1967
1967

6
0
1968
1968

7
0
1926
1926

7
3
2018
1926

7
2
2012
1926

7
1
2009
1926

8
0
1956
1956

9
0
1990
1990

10
0
1953
1904

10
1
1904
1904

Now you can apply LISTAGG to that row set and exclude BJA from the aggregation where it matches CONSTRYEAR. To exclude a year, you could use a CASE expression like this:

CASE BJA WHEN CONSTRYEAR THEN NULL ELSE BJA END

Or you could use an equivalent NULLIF shorthand:

NULLIF(BJA, CONSTRYEAR)

It works exactly like the CASE expression above.

Now you want to put that expression inside LISTAGG:

LISTAGG(NULLIF(BJA, CONSTRYEAR), ', ') WITHIN GROUP (ORDER BY BJA ASC) AS RECONSTRYEAR

Here is the full query:

SELECT
  FID
, CONSTRYEAR
, LISTAGG(NULLIF(BJA, CONSTRYEAR), ', ') WITHIN GROUP (ORDER BY BJA ASC) AS RECONSTRYEAR
FROM
  (
    SELECT
      YEARS.*
    , MIN(BJA) OVER (PARTITION BY FID) AS CONSTRYEAR
    FROM
      YEARS
  )
GROUP BY
  FID
, CONSTRYEAR
;

Output:

FID
CONSTRYEAR
RECONSTRYEAR

1
1949
null

2
1996
null

3
1970
null

4
1871
1975

5
1967
null

6
1968
null

7
1926
2009, 2012, 2018

8
1956
null

9
1990
null

10
1904
1953

Live demos are available:

• SQL Fiddle

• db<>fiddle

StackExchange Database Administrators Q#286721, answer score: 2