Forum Stats

  • 3,769,696 Users
  • 2,253,009 Discussions
  • 7,875,155 Comments

Discussions

Sql query to get every row should be duplicated based on number

User_X9S6M
User_X9S6M Member Posts: 56 Red Ribbon

I/P

col

1

2

3

Result:

1

2

2

3

3

3

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Oct 9, 2021 5:13PM Accepted Answer

    @User_X9S6M I have to agree with the sentiments in this thread, possibly the laziest post I've seen in years. Zero effort, no explanation of how your results link to the inputs, no Oracle version info, no table creation and inserts. Absolute bare minimum. I think posts like this speak volumes about YOU, the individual.

    Anyway, I'm in a good mood and assuming I'm also a good mind reader and you want to repeat the inputs as many times as their value, here's a solution (12c or above) :

    select c.column_value as result
    from table(sys.odcinumberlist(1, 2, 3)) n
    cross apply (
      select column_value
      from dual
      where n.column_value > 0
      connect by level <= n.column_value 
    ) c
    
    

Answers

  • EdStevens
    EdStevens Member Posts: 28,529 Gold Crown
    edited Oct 9, 2021 1:39PM

    Seriously?

    SERIOUSLY?

    No one is looking over your shoulder. No one knows what you are working with. Put yourself in our place, then re-read your posting and ask yourself if you really think that is sufficient to communicate the problem we assume you are trying to solve.

    Post - as formatted text, not a picture - the DDL of the table(s) you are working with.

    Post - as formatted text, not a picture - the INSERT statements to populate that table with representative test data.

    Post some representation of the desired output.

    Describe the logic by which the output is derived from the posted test data.

    And show your own effort to solve the problem.

    Frank Kulash
  • User_X9S6M
    User_X9S6M Member Posts: 56 Red Ribbon

    Dear Stevens,

    Calm down. If you don't want to answer or view pls ignore it.

    Thanks

  • mathguy
    mathguy Member Posts: 10,157 Blue Diamond

    Dear <whatever your name>,

    Calm down. If you don't want to answer Ed Sevens's questions, pls. ignore them. But then, very few of us will be willing to help you.

    Thanks.

    Frank Kulash
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond

    Hi, @User_X9S6M

    Ed already mentioned this, but it's important enough to repeat: Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. 

    Always post your complete version (e.g. 18.4.0.0.0). There's a fairly new feature in Oracle that helps with problems like yours, but without knowing your version, we can't tell if you can use it or not.

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Oct 9, 2021 5:13PM Accepted Answer

    @User_X9S6M I have to agree with the sentiments in this thread, possibly the laziest post I've seen in years. Zero effort, no explanation of how your results link to the inputs, no Oracle version info, no table creation and inserts. Absolute bare minimum. I think posts like this speak volumes about YOU, the individual.

    Anyway, I'm in a good mood and assuming I'm also a good mind reader and you want to repeat the inputs as many times as their value, here's a solution (12c or above) :

    select c.column_value as result
    from table(sys.odcinumberlist(1, 2, 3)) n
    cross apply (
      select column_value
      from dual
      where n.column_value > 0
      connect by level <= n.column_value 
    ) c
    
    
  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond

    And the old-skool way (oracle specific connect by clause)...

    SQL> ed
    Wrote file afiedt.buf
    
      1  with ip(col) as (select 1 from dual union select 2 from dual union select 3 from dual)
      2  -- end of input data
      3  select col
      4  from   ip
      5  connect by col = prior col
      6         and level <= col
      7*        and prior sys_guid() is not null
    SQL> /
    
           COL
    ----------
             1
             2
             2
             3
             3
             3
    
    6 rows selected.
    


    Also, I agree with the above. If you want better answers to your questions, you should make sure you post properly formatted details, and supply appropriate create table/insert statements (or a WITH clause) with your example data, and explain the logic and reasoning for what you want.