Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (id int, account int, extra_account text, state text, special_value int);
    
INSERT INTO tbl VALUES
  (100, 111, '111-1', 'WORKS', 1)
, (200, 111, '111-2', 'WORKS', 1)
, (300, 222, '222-1', 'WORKS', 2)
, (400, 333, '333-1', 'WORKS', 3)
, (500, 333, '333-2', 'WORKS', 4)
, (600, 333, '333-3', 'WORKS', 4)  -- should be included per org definition
;
6 rows affected

-- That's what you want per account:
SELECT account
    , CASE WHEN count(DISTINCT special_value) = 1
           THEN min(extra_account)
           ELSE string_agg(extra_account, ', ')
           END AS special_values
FROM   tbl
WHERE  account IN (111, 222, 333)
AND    state = 'WORKS'
GROUP  BY 1;


account
special_values


111
111-1

222
222-1

333
333-1, 333-2, 333-3


-- Wrap the above in a subquery and aggregate:
SELECT string_agg(special_values, ', ')
FROM (
   SELECT account
       , CASE WHEN count(DISTINCT special_value) = 1
              THEN min(extra_account)
              ELSE string_agg(extra_account, ', ')
              END AS special_values
   FROM   tbl
   WHERE  account IN (111, 222, 333)
   AND    state = 'WORKS'
   GROUP  BY 1
   ) sub;


string_agg


111-1, 222-1, 333-1, 333-2, 333-3


-- If you really want a single extra_account per distinct special_value:
SELECT string_agg(extra_account, ', ')
FROM  (SELECT DISTINCT ON (account, special_value) extra_account FROM tbl) sub;


string_agg


111-1, 222-1, 333-1, 333-2