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 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:
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:
See db fiddle