14 Replies Latest reply on Nov 8, 2010 8:37 PM by JustinCave

    Query need return data on a single record set.

    689662
      I have some select that return 7-10 rows:

      select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual union all
      select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual union all
      select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual
      ...
      ...

      I need get this data in 1 row:

      select 'gtrujk 4567 Kytyt Kfhf Jtdtd 234-765-8765 fdgdt45@someemail.com lowgtr 4567 Kyrt Adsresre Wdsdsf 654-652-8769 yfyufyu6787@someemail.com jiuyop 4567 Sertytfd Zfdsf Qfsdfs 609-345-6512 fhgdhf4@someemail.com' from dual
        • 1. Re: Query need return data on a single record set.
          Solomon Yakobson
          Use STRAGG function. There are plenty of examples on the web and on this forum. If aggregation can exceed 4000 bytes use CLOBAGG - same as STRAGG, just agrument is CLOB:
          with t as (
          select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual union all
          select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual union all
          select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual
          )
          select  substr(clobagg(' ' || col1 || ' ' || col2 || ' ' || col3 || ' ' || col4 || ' ' || col5 || ' ' || col6 || ' ' || col7),2) str
            from  t
          /
          
          STR
          -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          gtrujk 4567 Kytyt Kfhf Jtdtd 234-765-8765 fdgdt45@someemail.com lowgtr 4567 Kyrt Adsresre Wdsdsf 654-652-8769 yfyufyu6787@someemail.com jiuyop 4567 Sertytfd Zfdsf Qfsdfs 609-345-6512 fhgdhf4@someemail.com
          
          SQL> 
          SY.
          • 2. Re: Query need return data on a single record set.
            689662
            I used CLOBAGG and STRAGG:


            with t as (
            select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual union all
            select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual union all
            select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual
            )
            select substr(STRAGG(' ' || col1 || ' ' || col2 || ' ' || col3 || ' ' || col4 || ' ' || col5 || ' ' || col6 || ' ' || col7),2) str
            from t

            But I'm getting error:
            ora-00904: "STRAGG" invalid identifier

            How else I can solve this problem?
            • 3. Re: Query need return data on a single record set.
              Solomon Yakobson
              STRAGG/CLOBAGG aren't Oracle supplied functions. They are user defined functions based on ODCI interface. Anyway:
              create or replace
                type clobagg_type as object(
                                            text clob,
                                            static function ODCIAggregateInitialize(
                                                                                    sctx in out clobagg_type
                                                                                   )
                                              return number,
                                            member function ODCIAggregateIterate(
                                                                                 self  in out clobagg_type,
                                                                                 value in     clob
                                                                                )
                                              return number,
                                            member function ODCIAggregateTerminate(
                                                                                   self        in     clobagg_type,
                                                                                   returnvalue    out clob,
                                                                                   flags       in     number
                                                                                  )
                                              return number,
                                            member function ODCIAggregateMerge(
                                                                               self in out clobagg_type,
                                                                               ctx2 in     clobagg_type
                                                                              )
                                              return number
                                           );
              /
              create or replace
                type body clobagg_type
                  is
                    static function ODCIAggregateInitialize(
                                                            sctx in out clobagg_type
                                                           )
                      return number
                      is
                      begin
                          sctx := clobagg_type(null) ;
                          return ODCIConst.Success ;
                    end;
                    member function ODCIAggregateIterate(
                                                         self  in out clobagg_type,
                                                         value in     clob
                                                        )
                      return number
                      is
                      begin
                          self.text := self.text || value ;
                          return ODCIConst.Success;
                    end;
                    member function ODCIAggregateTerminate(
                                                           self        in     clobagg_type,
                                                           returnvalue    out clob,
                                                           flags       in     number
                                                          )
                      return number
                      is
                      begin
                          returnValue := self.text;
                          return ODCIConst.Success;
                      end;
                    member function ODCIAggregateMerge(
                                                       self in out clobagg_type ,
                                                       ctx2 in     clobagg_type
                                                      )
                      return number
                      is
                      begin
                          self.text := self.text || ctx2.text;
                          return ODCIConst.Success;
                      end;
              end;
              /
              create or replace
                function clobagg(
                                 input clob
                                )
                  return clob
                  deterministic
                  parallel_enable
                  aggregate using clobagg_type;
              /
              SY.
              • 4. Re: Query need return data on a single record set.
                689662
                How I can do same with using -- ROW_NUMBER() OVER PARTITION BY ORDER BY ?

                Can you fix my errors in this script?

                WITH t AS (select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual union all
                select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual union all
                select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual)

                select
                SELECT MAX(DECODE(rn), 1 col1),
                SELECT MAX(DECODE(rn), 1 col2),
                SELECT MAX(DECODE(rn), 1 col3),
                SELECT MAX(DECODE(rn), 2 col1),
                SELECT MAX(DECODE(rn), 2 col2),
                SELECT MAX(DECODE(rn), 2 col3),
                email
                FROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY col7 ORDER BY col1) rn from t )

                GROUP BY email
                • 5. Re: Query need return data on a single record set.
                  JustinCave
                  WITH t AS (
                    select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual 
                    union all
                    select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual 
                    union all
                    select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual)
                  select
                         MAX(DECODE(rn, 1, col1, null)),
                         MAX(DECODE(rn, 1, col2, null)),
                         MAX(DECODE(rn, 1, col3, null)),
                         MAX(DECODE(rn, 2, col1, null)),
                         MAX(DECODE(rn, 2, col2, null)),
                         MAX(DECODE(rn, 2, col3, null)),
                         col7
                  FROM (SELECT t.*, ROW_NUMBER() OVER(PARTITION BY col7 ORDER BY col1) rn from t ) 
                  GROUP BY col7
                  fixes the syntax errors. But that returns three rows since you have three different email addresses.

                  How do you know that you want to combine these three rows? They don't appear to have anything in common.
                  How do you know which columns the different data elements should be placed in? Why does one row in T go before another in the result?

                  Justin
                  • 6. Re: Query need return data on a single record set.
                    Sayan Malakshinov.
                    user10886774, as i wrote:
                    create or replace function as_one_row(p_tab_name in varchar2,p_col_name in varchar2)
                     return sys_refcursor is
                      Result sys_refcursor;
                      query varchar2(4000);
                      cnt number;
                      tab_name varchar2(4000):='(select * from '||p_tab_name||' order by '||p_col_name||')';
                    begin
                      query:='select * from '||tab_name||' t1';
                      execute immediate 'select count(1) from '||p_tab_name into cnt;
                      for i in 2..cnt loop
                        query:=query||' left join '||tab_name||' t'||to_char(i)||
                                             ' on t'||to_char(i-1)||'.'||p_col_name||
                                             '<'||
                                             't'||to_char(i)||'.'||p_col_name;
                      end loop;
                      query:=query||' where rownum<=1';
                      open result for query;
                      return(Result);
                    end as_one_row;
                    or fixed your code:
                    WITH t AS (select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual union all
                    select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual union all
                    select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual)
                    
                    SELECT 
                     MAX(DECODE(n, 1, col1)),
                     MAX(DECODE(n, 1, col2)),
                     MAX(DECODE(n, 1, col3)),
                     MAX(DECODE(n, 2, col1)),
                     MAX(DECODE(n, 2, col2)),
                     MAX(DECODE(n, 2, col3))
                    FROM 
                     (select rownum n,t.* from t)
                    GROUP BY 1
                    • 7. Re: Query need return data on a single record set.
                      689662
                      Justin,

                      I need move all records into 1 row, like:

                      select 'gtrujk 4567 Kytyt Kfhf Jtdtd 234-765-8765 fdgdt45@someemail.com lowgtr 4567 Kyrt Adsresre Wdsdsf 654-652-8769 yfyufyu6787@someemail.com jiuyop 4567 Sertytfd Zfdsf Qfsdfs 609-345-6512 fhgdhf4@someemail.com' from dual
                      • 8. Re: Query need return data on a single record set.
                        JustinCave
                        I understand you want to combine all three rows into a single row. That's why I asked the questions I asked.

                        - How do you know that these three rows should be combined? Normally, there is some common attribute that you can use to group the three rows.
                        - How do you know what order to present the data? Hos do you know to present the fdgdt45@someemail.com data before the yfyufyu6787@someemail.com data, for example?

                        Justin
                        • 9. Re: Query need return data on a single record set.
                          689662
                          For example output:

                          select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual union all
                          select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual union all
                          select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual

                          I need get this data in 1 row like this:

                          select 'gtrujk 4567 Kytyt Kfhf Jtdtd 234-765-8765 fdgdt45@someemail.com lowgtr 4567 Kyrt Adsresre Wdsdsf 654-652-8769 yfyufyu6787@someemail.com jiuyop 4567 Sertytfd Zfdsf Qfsdfs 609-345-6512 fhgdhf4@someemail.com' from dual

                          Justin, Each next row going in one row -- one after one
                          • 10. Re: Query need return data on a single record set.
                            Sayan Malakshinov.
                            Use Solomon's solution or use wm_concat, but with wm_concat you could have trouble with comma in fields when you will replcase comma with space.
                            • 11. Re: Query need return data on a single record set.
                              JustinCave
                              Justin, Each next row going in one row -- one after one
                              But that requires that you do some sort of ORDER BY. Otherwise, the order that rows are returned is arbitrary. Do you care whether the 'gtrujk' data comes before the 'lowgtr' data? Is that because 'gtrujk' comes before 'lowgtr' alphabetically?

                              If you know that your query will always return only 3 rows of data (or that you only want to combine the data from the first three rows alphabetically by col1), you can simply eliminate the GROUP BY
                              WITH t AS (
                                select 'gtrujk' col1, '4567' col2, 'Kytyt' col3, 'Kfhf' col4, 'Jtdtd' col5, '234-765-8765' col6, 'fdgdt45@someemail.com' col7 from dual 
                                union all
                                select 'lowgtr', '4567', 'Kyrt', 'Adsresre', 'Wdsdsf', '654-652-8769', 'yfyufyu6787@someemail.com' from dual 
                                union all
                                select 'jiuyop', '4567', 'Sertytfd', 'Zfdsf', 'Qfsdfs', '609-345-6512', 'fhgdhf4@someemail.com' from dual)
                              select MAX( DECODE(rn, 1, col1, null) ),
                                     MAX( DECODE(rn, 1, col2, null) ),
                                     MAX( DECODE(rn, 1, col3, null) ),
                                     MAX( DECODE(rn, 2, col1, null) ),
                                     MAX( DECODE(rn, 2, col2, null) ),
                                     MAX( DECODE(rn, 2, col3, null) ),
                                     MAX( DECODE(rn, 3, col1, null) ),
                                     MAX( DECODE(rn, 3, col2, null) ),
                                     MAX( DECODE(rn, 3, col3, null) )
                              FROM (SELECT t.*, ROW_NUMBER() OVER(ORDER BY col1) rn from t ) 
                              Justin
                              • 12. Re: Query need return data on a single record set.
                                689662
                                Solomons solution is very useful and I use it now -- Thank you, Solomon !!!
                                But I think in fiture will be some problems because my procedures are working with CLOB data.
                                I want to find another solution with out CLOB data type.
                                • 13. Re: Query need return data on a single record set.
                                  689662
                                  Justin, but if I dont know how many rows will be? Some times can be 3 , sometimes 5,or 7 ...
                                  How I can include this condition in query? I know that always will be 7 columns and all rows I need to put in 1 recod set -- one by one .
                                  • 14. Re: Query need return data on a single record set.
                                    JustinCave
                                    If you want each result in a separate column and want to stick with static SQL, you'd have to define the maximum number of allowable rows to merge together (i.e. 7) and just return NULL for the last 4 row's worth of columns if you only had 3 rows to merge. You could use dynamic SQL to construct a query with a dynamic number of columns depending on the number of rows in the query but that gets pretty complex pretty quickly.

                                    Otherwise, you would want to concatenate all the data into a single column using something like Solomon's approach.

                                    It all depends on how the downstream code intends to consume this data.

                                    Justin