The Oracle (tm) Users' Co-Operative FAQ

How do I implement a “STRAGG” Solution in an SQL statement?

Author's name: Frank Zhou


Date written: 22th November 2006

Oracle version(s): 10.2

How do I implement a “STRAGG”  solution in a SQL Statement

Back to index of questions

The following SQL Model Clause Pattern can be used to implement a “STRAGG  Solution in a single SQL query.

SQL>  select * from t1;


DE NAME                                                                        

-- ----------                                                                  

D1 Simon                                                                       

D1 Jon                                                                          

D1 Frank                                                                       

D2 Peter                                                                       

D2 Adam                                                                         

D3 Tom                                                                         

D3 Lee                                                                         

D3 Jack                                                                         


  SELECT dept, name_Str


     (SELECT dept, name_Str, rn, counter

      FROM t1


      PARTITION BY (dept)

      DIMENSION BY (row_number() over (PARTITION BY dept ORDER BY rowid) rn )

      MEASURES (name, CAST(NULL AS VARCHAR2(3999)) name_Str,

      count(*) OVER  (PARTITION BY dept) counter


    RULES (

    name_Str[ANY] ORDER BY rn  =

                  CASE WHEN name[cv() - 1 ] IS NULL

              THEN name[cv()]

                       ELSE name_Str[cv()-1]||','|| name[cv()]




   WHERE  counter = rn

   ORDER BY dept;


DE      NAME_STR                                                                        

--   -------------------

D1    Simon,Jon,Frank                                                                            

D2    Peter,Adam                                                                          

D3    Tom,Lee,Jack                                                                                                                                                    



The following is a 9I Solution :                                                                                


 SELECT dept, MAX(LTRIM(sys_connect_by_path(name, ',' ) ,  ',')) name_str



          SELECT dept, name,

                row_number() over (PARTITION BY dept ORDER BY ROWID) rn

          FROM t1


       START WITH rn = 1

       CONNECT BY dept = PRIOR dept AND  PRIOR  rn  = rn -1

      GROUP BY dept



DE      NAME_STR                                                                        

--   -------------------

D1    Simon,Jon,Frank                                                                           

D2    Peter,Adam                                                                          

D3    Tom,Lee,Jack                                                                    



SQL> spool off



Back to top

Back to index of questions