0
Can we expand a column storing number somehow in the output.
I am trying to expand one column with other column having same value.

column which is i am trying is number.. like if the value is 3 then result in select query should have 1,2,3 with other column having same data. Is that possible?

For ex,

INPUT TABLE IS 

    Can we expand a column storing number somehow in the output.
    For ex, <br>
    <h4>INPUT TABLE IS :</h4>
     
    <table style="width:50%" border=1>
      <tr>
        <td>Thank</td>
        <td>You </td> 
        <td>2 </td>
      </tr>
      <tr>
    </table>
    <br>
    <h4>Output TABLE should be like this :</h4>
     
    <table style="width:50%" border=1>
      <tr>
        <td>Thank</td>
        <td>You </td> 
        <td>1 </td>
      </tr>
    <tr><br>
        <td>Thank</td>
        <td>You </td> 
        <td>2 </td>
      </tr>
      <tr>
    </table>
    <br><br>
    value of last column is number and has expanded.<br>
    <b>Column1 and column2 should contain same value.</b><br>
    
    The problem is that i can change the input table and this is how i need the output.<br>
    I am trying using dual table join with table1 and use CONNECT BY but not getting the result . 

<br> Any help on this..

Output table is

value of last column is number and has expanded. Column1 and column2 should contain same value.

The problem is that i can change the input table and this is how i need the output.
I am trying using dual join with table1 and use CONNECT BY but not getting the result .
Rahul
  • 95
  • 1
  • 3
  • 14
  • Okay, not sure I am understanding this correctly. This is what it sounds like to me. "I want to input a number. Then I want the output table to have as many rows as the number I entered, but with the third column representing a count of the rows from '1' to 'n' where n is the number I entered. Is that correct? – guildsbounty Oct 01 '14 at 21:11
  • my bad for phrasing question poorly. So, i already have a table with data in it. Need to execute a select query which actually expand column3 (last column) with other column having same data for that value. Ex, column3 is = 4 then select query should give result as 4 row which should be 1,2,3,4 as column3 and column1 , column2 should be exactly same.. – Rahul Oct 01 '14 at 21:17
  • What's the point of having HTML markup in your question? –  Oct 02 '14 at 13:01

1 Answers1

0

Okay, here is the logic to get your 'count' column...

SELECT LEVEL AS Column3
FROM DUAL
CONNECT BY LEVEL <= @EnteredNumber;

This will create a table of sequential numbers running from 1 to the number you entered. All you have to do is join this new table with your pre-made results.

How this works is that it creates a self-referencing loop that produces 'inheritance' where each subsequent row is considered a child of the prior one. It's weird, yes...but it should work

Editing to adjust my answer to work with the specifics you asked for in the comment:

SELECT t.Column1, t.Column2, LEVEL As Column3 
FROM (
    SELECT Column1, Column2, Column3Value 
    FROM Table_1  
    WHERE t.column1='value' 
    AND t.column2 = 'value2'
) t 
CONNECT BY LEVEL <= t.Column3Value

Make sure you do your WHERE work in the subquery, otherwise it is going to run the connecting against the entire table before filtering, and that will be terrible for your performance.

guildsbounty
  • 3,326
  • 3
  • 22
  • 34
  • okay but @EnteredNumber is coming from another table which is TABLE_1. I tried joining this table with column of that table but error coming up. – Rahul Oct 01 '14 at 21:43
  • SELECT LEVEL AS Column3 FROM DUAL ,TABLE_1 t WHERE t.column1='value' AND t.column2 = 'value2' CONNECT BY LEVEL <= @column3Value – Rahul Oct 01 '14 at 21:44
  • Added a second block showing the specifics of how to apply this logic to your case. – guildsbounty Oct 02 '14 at 12:54