9 Replies Latest reply: Dec 27, 2012 11:43 PM by NSK2KSN RSS

    Need output as below

    763028
      Hi I have a table t1
      with t1 as 
      (
      select 'A1' pkey, 2 counts from dual
      union all
      select 'A2', 3 from dual
      union all
      select 'A3', 5 from dual
      )
      select * from t1;
      
      pkey   Counts 
      ------   ----------
      A1    2
      A2    3
      A4    5
      
      
      Need output as below
      
      pkey   Counts   num
      ------   ----------    --------
      A1    2             01
      A1    2             02  
      A2    3             01
      A2    3             02
      A2    3             03
      A4    5             01
      A4    5             02
      A4    5             03
      A4    5             04
      A4    5             05
      
      If Counts is 2 then result should be two rows with num sequence, 
      Edited by: user12863561 on Dec 26, 2012 3:08 AM
        • 1. Re: Need output as below
          Purvesh K
          This way:
          with t1 as 
          (
          select 'A1' pkey, 2 counts from dual
          union all
          select 'A2', 3 from dual
          union all
          select 'A3', 5 from dual
          )
          select pkey
            from t1
          connect by level <= counts
                 and pkey = prior pkey
                 and prior sys_guid() is not null;
          
          PKEY 
          ---- 
          A1   
          A1   
          A2   
          A2   
          A2   
          A3   
          A3   
          A3   
          A3   
          A3   
          
           10 rows selected 
          • 2. Re: Need output as below
            763028
            Thanks purvesh, for your reply, can you please explain me the query and also how to get 01, 02 for counts 2, 01, 02 and 03 for counts 3
            • 3. Re: Need output as below
              Karthick_Arp
              to get the count just include the LEVEL pseudocolumn in the SELECT clause.
              • 4. Re: Need output as below
                763028
                tht worked fine, thanks karthick, just looking if we can achcieve this through sql with out using level/inner queries like tht,
                • 5. Re: Need output as below
                  Purvesh K
                  user12863561 wrote:
                  Thanks purvesh, for your reply, can you please explain me the query and also how to get 01, 02 for counts 2, 01, 02 and 03 for counts 3
                  with t1 as 
                  (
                  select 'A1' pkey, 2 counts from dual
                  union all
                  select 'A2', 3 from dual
                  union all
                  select 'A3', 10 from dual
                  )
                  select pkey, 
                         to_char(level, 'fm09') num,
                         lpad(level, 2, '0') num_1
                    from t1
                  connect by level <= counts
                         and pkey = prior pkey
                         and prior sys_guid() is not null;
                  
                  PKEY NUM NUM_1 
                  ---- --- ----- 
                  A1   01  01    
                  A1   02  02    
                  A2   01  01    
                  A2   02  02    
                  A2   03  03    
                  A3   01  01    
                  A3   02  02    
                  A3   03  03    
                  A3   04  04    
                  A3   05  05    
                  A3   06  06    
                  A3   07  07    
                  A3   08  08    
                  A3   09  09    
                  A3   10  10    
                  
                   15 rows selected 
                  • 6. Re: Need output as below
                    Karthick_Arp
                    user12863561 wrote:
                    tht worked fine, thanks karthick, just looking if we can achcieve this through sql with out using level/inner queries like tht,
                    may be
                    with t1 as 
                    (
                      select 'A1' pkey, 2 counts from dual
                      union all
                      select 'A2', 3 from dual
                      union all
                      select 'A3', 5 from dual
                    )
                    select *
                      from t1
                     cross 
                      join 
                           (
                            select rownum rno
                              from all_objects
                             where rownum <= (select max(counts) from t1)
                           )
                     where rno <= counts
                     order 
                        by pkey, rno 
                    /    
                    Just make sure all_objects has rows greater than or exual to max(counts) :)
                    • 7. Re: Need output as below
                      myOra_help
                      Try this,
                       WITH t1 AS 
                      (
                      SELECT 'A1' pkey, 2 counts FROM dual
                      UNION ALL
                      SELECT 'A2', 3 FROM dual
                      UNION ALL
                      SELECT 'A3', 5 FROM dual
                      ),
                      t2 AS (SELECT LEVEL rn FROM dual CONNECT BY LEVEL <=(SELECT MAX(counts) FROM t1))
                      SELECT  pkey ,counts , LPAD(rn, 2, '0') "NUM"FROM t1,t2
                      WHERE rn<=counts
                      ORDER BY pkey,rn;
                      
                      Output
                      ----------------
                      PKEY     COUNTS     NUM
                      A1     2     01
                      A1     2     02
                      A2     3     01
                      A2     3     02
                      A2     3     03
                      A3     5     01
                      A3     5     02
                      A3     5     03
                      A3     5     04
                      A3     5     05
                      • 8. Re: Need output as below
                        ranit B
                        select pkey,
                        to_char(level, 'fm09') num,
                        lpad(level, 2, '0') num_1
                        from t1
                        connect by level <= counts
                        and pkey = prior pkey
                        and prior sys_guid() is not null;
                        Hi Purvesh,

                        Can you please explain the working of this query?
                        To be specific, what is this sys_guid()* and how exactly it works?

                        Help highly appreciated. TIA.

                        Ranit B.
                        • 9. Re: Need output as below
                          NSK2KSN
                          Please explain the usage of sys_guid() and how exactly it works?