Transposing Column into Rows

There might be cases when we would like to aggregate the data into a single row for the set of records for a particular value, for example say i have following data in my table


FILTERID               CRITERIA           
---------------------- --------------------
28900                  CRIT1              
28901                  CRIT1              
28902                  CRIT1              
28903                  CRIT1              
28904                  CRIT2              
28905                  CRIT2              
28906                  CRIT2              
28907                  CRIT3              
28908                  CRIT3  


I would like to display it as :

CRITERIA             FILTERIDS
------------------------------------
CRIT1                28900,28901,28902,28903
CRIT2                28904,28905,28906
CRIT3                28907,28908    

You can do it using the following ways :

1. WM_CONCAT Built-in Function
2. Using analytic function with SYS_CONNECT_BY_PATH


Creating sample data

create table test_data
(
filterid number,
criteria varchar2(20),
val    number
);

insert into test_data values (28900,'CRIT1',1);
insert into test_data values (28901,'CRIT1',2);
insert into test_data values (28902,'CRIT1',3);
insert into test_data values (28903,'CRIT1',4);
insert into test_data values (28904,'CRIT2',1);
insert into test_data values (28905,'CRIT2',2);
insert into test_data values (28906,'CRIT2',3);
insert into test_data values (28907,'CRIT3',1);
insert into test_data values (28908,'CRIT3',2);
commit;

1. Using WM_CONCAT function .

select criteria,wm_concat(filterid) FILTERIDS from test_data   group by criteria

2. Using analytic function with SYS_CONNECT_BY_PATH

SELECT     criteria
        , SUBSTR(MAX (SYS_CONNECT_BY_PATH (filterid, ',')),2)
                 filterid_final
      FROM (SELECT criteria, filterid
                , RANK () OVER (PARTITION BY criteria ORDER BY filterid) rn
             FROM test_data)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND PRIOR criteria = criteria
  GROUP BY criteria
  ORDER BY criteria