This discussion is archived
9 Replies Latest reply: Dec 27, 2012 9:43 PM by NSK2KSN RSS

Need output as below

763028 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    to get the count just include the LEVEL pseudocolumn in the SELECT clause.
  • 4. Re: Need output as below
    763028 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Please explain the usage of sys_guid() and how exactly it works?

Legend

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