The Oracle (tm) Users' Co-Operative FAQ

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


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

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

    from

     (SELECT dept, name_Str, rn, counter

      FROM t1

      MODEL

      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()]

         END

     )

    )

   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

        FROM

        (

          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

      ORDER 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