Concatenating only desirable values with LISTAGG

listaggoracle-sql-developerquery

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       |
| ... |    ...    |       ...        |
+-----+-----------+------------------+

snippet_of_table

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 INTO YEARS (
    "FID","I0","BJA"
)
VALUES (
    (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 am trying to execute the following query:

SELECT YEARS.FID,
       MIN(YEARS.BJA) AS "CONSTRYEAR",
       LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC) AS "RECONSTRYEAR"
FROM DB.YEARS YEARS
GROUP BY YEARS.FID;

And the output of the above query:

snippet_of_result

However, it is not the result that I want … Yes, I am reading documentation source 1, source 2, and source 3; and also I have seen related threads How to use LISTAGG and WHERE together and How to use Oracle's LISTAGG function with a unique filter?.

How can I get the result looking like this:

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

Where you see that values from "CONSTRUCT" column were excluded from "RECONSTRYEAR". I do not understand where do I need to put a WHERE YEARS.IO != 0 clause in LISTAGG, so the lowest year won't be included.

Best Answer

Regexp_replace goes a long way:

 Regexp_replace( LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC),
                   '^[0-9]*(, )?','')

See db fiddle