6 Replies Latest reply: Feb 25, 2014 10:04 AM by krmarshall87 RSS

Combine similar rows

krmarshall87 Newbie
Currently Being Moderated

For example,

select s.sid, s.username, t.SQL_TEXT
from gv$sqltext t, gv$session s
where s.sql_address=t.address
order by s.sid, t.piece;

 

 

which will return a SID, Username, and 64 characters of the sql, the next row will show 64 characters and so on.

 

So I would like to be able to combine all t.SQL_TEXT where s.SID and s.USERNAME are the same as the previous row, as such:

 

Before

 

SID      USERNAME          SQL_TEXT

1          USER1                  select blah blah blah blah 64 characters long

1          USER1                  second set of 64 character to 128

1          USER1                  long query 64 more characters

1          USER1                  still going 64 more characters more

 

After

1          USER1                    select blah blah blah blah 64 characters longsecond set of 64 character to 128long query 64 more charactersstill going 64 more characters more

 

(Note:  No spaces between SQL_TEXT strings.  If they should be there, they are considered a character)

 

Thanks for the help!

  • 1. Re: Combine similar rows
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    That sounds like a job for the LISTAGG function, something like this:

    SELECT    s.sid, s.username

    ,         LISTAGG (t.sql_text, NULL) WITHIN GROUP (ORDER BY t.piece) AS sql_text

    FROM      gv$sqltext  t

    ,         gv$session  s

    WHERE     s.sql_address  = t.address

    GROUP BY  s.sid, s.username

    ORDER BY  s.sid, s.username

    ;

    LISTAGG returns a VARCHAR2, which can be up to 4000 characters long.  If all of the strings to be combined are the full 64 characters long, then you can have as many as 62 of them in each group.

  • 2. Re: Combine similar rows
    Karthick_Arp Guru
    Currently Being Moderated

    What is the version of your Oracle Database? Because the solution will differ based on the version. Please look into the FAQ Re: 4. How do I convert rows to columns? (Look for String Aggregation).

     

    Said that, You need to understand the maximum size of VARCHAR2 in SQL is 4000 (Bytes/Characters). So if your SQL text length exceeds 4000 then you will have problem. So if you can explain the objective behind this requirement we could help you better.

  • 3. Re: Combine similar rows
    michaelrozar17 Pro
    Currently Being Moderated

    Alternatively,using MODEL clause:

    Reference: About Oracle: String aggregation with the model clause

    select sid

          ,username

          ,sqll sql_text

        from

        (select sid

               ,username

               ,sqll

               ,piece

            from gv$sqltext t, gv$session s 

            where s.sql_address=t.address

            MODEL

                PARTITION BY (sid,username)

                DIMENSION BY (piece)

                MEASURES (cast(sql_text as varchar2(4000))as sqll)

                RULES (sqll[any] order by piece desc = sqll[cv()]||''||sqll[cv()+1]

                      )

        )

        where piece=0

  • 4. Re: Combine similar rows
    krmarshall87 Newbie
    Currently Being Moderated

    I was hoping there may be a version independent query as I have both 10g and 11g databases.  The use for this would be to determine the sql based upon sid for a blocking session.  Perhaps others know of a simpler way.

     

    For a single SID query, using Frank's suggestion, it takes 8.44s on a relatively large 11g database.  For Michael's; 9.64s.

  • 5. Re: Combine similar rows
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    It's a good idea to say what Oracle version(s) you're using up front, when you first post the question.

     

    LISTAGG was new in Oracle 11.2, so it won't help you in Oracle 10.

    The generic name for what you want is String Aggregation.  This page:

    http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

    show several ways to do string aggregation.  LISTAGG is the only one that won't work in Oracle 10.

    SInce you need to concatente the strings in a particular order, I recommend the SYS_CONNECT_BY_PATH technique, which was new in Oracle 9.1, and got easier to use in Oracle 10.1.  Unfortunately, the oracle-base page cited above still shows the clunky, Oracle 9 way to do it.  For the simpler (and more efficient) Oracle 10 form, see the comments page that accompanies that oracle-base page:

    http://www.oracle-base.com/misc/Comments.php?page_id=122

  • 6. Re: Combine similar rows
    krmarshall87 Newbie
    Currently Being Moderated

    Thanks!  I'll look into it!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points