HAVING a IN (SELECT DISTINCT(a) FROM abcd) 

create table abcd ( a char(1) );

insert into abcd values ( 'A' );
insert into abcd values ( 'B' );
insert into abcd values ( 'D' );
insert into abcd values ( 'A' );
insert into abcd values ( 'C' );
insert into abcd values ( 'D' );
insert into abcd values ( 'A' );

column result format a40

select result
from
(
  select
    position ,
    a || '|' || a_count ||
    lead( '|' || a || '|' || a_count, 1 ) over ( order by a ) ||
    lead( '|' || a || '|' || a_count, 2 ) over ( order by a ) ||
    lead( '|' || a || '|' || a_count, 3 ) over ( order by a ) ||
    lead( '|' || a || '|' || a_count, 4 ) over ( order by a ) as result
  from
    ( select a
      ,      row_number() over ( order by a ) as position
      ,      to_char(count(a)) a_count
      from   abcd
      group  by a
    )
)
where position = 1 ;

RESULT
----------------------------------------
A|3|B|1|C|1|D|2

select MAX('A') A,
       sum(decode(a, 'A', 1, 0)) A_COUNT,
       MAX('B') B,
       sum(decode(a, 'B', 1, 0)) B_COUNT,
       MAX('C') C,
       sum(decode(a, 'C', 1, 0)) C_COUNT,
       MAX('D') D,
       sum(decode(a, 'D', 1, 0)) D_COUNT
  from abcd;

CREATE OR REPLACE FUNCTION get_single_line_data() RETURN VARCHAR2 IS
   CURSOR c_data IS
      SELECT a, COUNT(*) acnt FROM abcd GROUP BY a ORDER BY a;
   v_data VARCHAR2(4000);      
BEGIN
   FOR i IN c_data LOOP
      v_data := v_data||' '||i.a||' | '||i.a_cnt||' |';
   END LOOP;
   v_data := RTRIM(v_data, ' |');
   RETURN v_data;
END;

SELECT get_single_line_data() FROM dual;

SQL> create table abcd 
  2  as select substr(object_name,1,1) a from dba_objects
  3  /

Table created.

SQL> CREATE OR REPLACE FUNCTION get_single_line_data_1 RETURN VARCHAR2 IS
  2     CURSOR c_data IS
  3        SELECT a, COUNT(*) acnt FROM abcd GROUP BY a ORDER BY a;
  4     v_data VARCHAR2(4000);      
  5  BEGIN
  6     FOR i IN c_data LOOP
  7        v_data := v_data||i.a||'|'||i.acnt||'|';
  8     END LOOP;
  9     v_data := RTRIM(v_data, '|');
 10     RETURN v_data;
 11  END;
 12  /

Function created.

SQL> CREATE OR REPLACE FUNCTION get_single_line_data_2 RETURN VARCHAR2 IS
  2     type string_array is table of varchar2(20) index by binary_integer;
  3     v_sa string_array;
  4     v_data VARCHAR2(4000);      
  5  BEGIN
  6     SELECT a||'|'||COUNT(*) acnt 
  7     BULK COLLECT INTO v_sa
  8     FROM abcd GROUP BY a ORDER BY a;
  9     FOR i IN 1..v_sa.count LOOP
 10        v_data := v_data||v_sa(i)||'|';
 11     END LOOP;
 12     v_data := RTRIM(v_data,'|');
 13     RETURN v_data;
 14  END;
 15  /

Function created.

SQL> SELECT get_single_line_data_1 result FROM dual;
RESULT
----------------------------------------------------------------------------------------------------
/|11367|A|552|B|5|C|58|D|1411|E|427|F|9|G|542|H|207|I|289|J|26|K|344|L|519|M|72|N|13|O|166|P|87|Q|3|
R|219|S|322|T|45|U|504|V|544|W|6|X|61|_|29|c|94|j|2600|m|10|o|1793|s|2263

1 row selected.

SQL> SELECT get_single_line_data_2 result FROM dual;
RESULT
----------------------------------------------------------------------------------------------------
/|11367|A|552|B|5|C|58|D|1411|E|427|F|9|G|542|H|207|I|289|J|26|K|344|L|519|M|72|N|13|O|166|P|87|Q|3|
R|219|S|322|T|45|U|504|V|544|W|6|X|61|_|29|c|94|j|2600|m|10|o|1793|s|2263

1 row selected.

SQL> set timing on
SQL> declare
  2    result varchar2(4000);
  3  begin
  4    for i in 1..1000 loop
  5      SELECT get_single_line_data_1 into result FROM dual;
  6    end loop;
  7  end;
  8  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.09
SQL> declare
  2    result varchar2(4000);
  3  begin
  4    for i in 1..1000 loop
  5      SELECT get_single_line_data_2 into result FROM dual;
  6    end loop;
  7  end;
  8  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.12

SQL> select replace(substr(sys_connect_by_path(acnt,'|'),2),'#','|') result
  2  from (
  3  select a||'#'||count(*) acnt,
  4         row_number() over (order by a) rn,
  5         count(*) over () cnt
  6  from abcd 
  7  group by a
  8  )
  9  where rn = cnt
 10  connect by prior rn = rn-1
 11  start with rn = 1
 12  /
RESULT
----------------------------------------------------------------------------------------------------
/|11367|A|552|B|5|C|58|D|1411|E|427|F|9|G|542|H|207|I|289|J|26|K|344|L|519|M|72|N|13|O|166|P|87|Q|3|
R|219|S|322|T|45|U|504|V|544|W|6|X|61|_|29|c|94|j|2600|m|10|o|1793|s|2263

1 row selected.

SQL> set timing on
SQL> declare
  2    result varchar2(4000);
  3  begin
  4    for i in 1..1000 loop
  5      select replace(substr(sys_connect_by_path(acnt,'|'),2),'#','|') 
  6      into result
  7      from (
  8      select a||'#'||count(*) acnt,
  9             row_number() over (order by a) rn,
 10             count(*) over () cnt
 11      from abcd 
 12      group by a
 13      )
 14      where rn = cnt
 15      connect by prior rn = rn-1
 16      start with rn = 1;
 17    end loop;
 18  end;
 19  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.78