Oracle 18c

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

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


Create table YEARS (rn number, fid number, io number, bja number);
insert into years values (1, 1, 0, 1949);
1 rows affected

insert into years values (2, 2, 0, 1996);
1 rows affected

insert into years values (3, 3, 0, 1970);
1 rows affected

insert into years values (4, 4, 1, 1871);
1 rows affected

insert into years values (5, 4, 0, 1975);
1 rows affected

insert into years values (6, 5, 0, 1967);
1 rows affected

insert into years values (7, 6, 0, 1968);
1 rows affected

insert into years values (8, 7, 0, 1926);
1 rows affected

insert into years values (9, 7, 3, 2018);
1 rows affected

insert into years values (10, 7, 2, 2012);
1 rows affected

insert into years values (11, 7, 1, 2009);
1 rows affected

insert into years values (12, 8, 0, 1956);
1 rows affected

insert into years values (13, 9, 0, 1990);
1 rows affected

insert into years values (14, 10, 1, 1904);
1 rows affected

insert into years values (15, 10, 0, 1953);
1 rows affected

SELECT YEARS.FID,
       MIN(YEARS.BJA) AS "CONSTRYEAR",
       LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC) AS "RECONSTRYEAR",
       Regexp_replace( LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC),
                       '^[0-9]*(, )?','') as kiekeboe
FROM YEARS
GROUP BY YEARS.FID;


FID
CONSTRYEAR
RECONSTRYEAR
KIEKEBOE


1
1949
1949
null

2
1996
1996
null

3
1970
1970
null

4
1871
1871, 1975
1975

5
1967
1967
null

6
1968
1968
null

7
1926
1926, 2009, 2012, 2018
2009, 2012, 2018

8
1956
1956
null

9
1990
1990
null

10
1904
1904, 1953
1953

FID	CONSTRYEAR	RECONSTRYEAR	KIEKEBOE
1	1949	1949	null
2	1996	1996	null
3	1970	1970	null
4	1871	1871, 1975	1975
5	1967	1967	null
6	1968	1968	null
7	1926	1926, 2009, 2012, 2018	2009, 2012, 2018
8	1956	1956	null
9	1990	1990	null
10	1904	1904, 1953	1953