Patrick Gregorio Patrick Gregorio - 3 months ago 9
SQL Question

Oracle SQL comma-separated id

I have a query like so:

SELECT
s.id,
CASE
WHEN INSTR(s.strain_value, '[') = 0
THEN s.strain_name
ELSE TRIM(SUBSTR(s.strain_name, 1, INSTR(s.strain_name, '[')-1))
END AS strain_name
FROM strain s


This gives me a table that looks like:

id strain_name
----------------------------------------------
100 CD-1
101 CD-1
102 CD-1
200 SCID
300 BusIp-hy
301 BusIp-hy
... ...


Now I wanted to group the resulting table by the
strain_name
and get the comma-separated
id
like the following:

id strain_name
----------------------------------------------
100, 101, 102 CD-1
200 SCID
300, 301 BusIp-hy
... ...


I tried this query out but I'm not getting what I wanted:

WITH q AS (
SELECT
s.id,
CASE
WHEN INSTR(s.strain_name, '[') = 0
THEN s.strain_name
ELSE TRIM(SUBSTR(s.strain_name, 1, INSTR(s.strain_name, '[')-1))
END AS strain_name
FROM strain s
)
SELECT (
SELECT LISTAGG(id, ', ')
WITHIN GROUP (
ORDER BY strain_name ASC
)
FROM (SELECT DISTINCT strain_name FROM q) dc
) AS id, strain_name
FROM q


Can you please help me correct the last query so that I can get a comma-separated list of the
id
for each
strain_name
?

MT0 MT0
Answer
WITH q ( id, strain_name ) AS (
  SELECT id,
         CASE WHEN INSTR(strain_value, '[') = 0
              THEN strain_name
              ELSE TRIM(SUBSTR(strain_name, 1, INSTR(strain_name, '[')-1))
         END
  FROM strain
)
SELECT LISTAGG( id, ', ' ) WITHIN GROUP ( ORDER BY id ) AS id,
       strain_name
FROM   q
GROUP BY strain_name