Display Same Record Multiple time from SQL Query

I would like to share a interview question where you have to display the same records multiple times using SQL query. This is very easy job with the help of PL/SQL block but here we will see, how we can do this using SQL query.


First check the sample data and desired output.

&nbsp; Text VARCHAR2(10) ,<br />
&nbsp; min NUMBER ,<br />
&nbsp;&nbsp;max NUMBER) ; &nbsp;&nbsp;<br />
&nbsp;<br />
INSERT INTO tmp&nbsp;VALUES ('AAA', 2,4) ;<br />
INSERT INTO tmp&nbsp;VALUES ('BBB', 25,28) ;<br />
INSERT INTO tmp VALUES ('CCC', 10,13) ;

Now come to solution , if somehow we get the series of number (like 1,2,3,4… Max) as a data set and then we can join this with original table to get desired output.
We can get this data set of number(like 1,2,3…) with the help of “Connect by Level” .
Here LEVEL is a pseudo column which returns 1 for a root row, 2 for a child of a root, and so on. CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. We use all these in hierarchical query. Will explain hierarchical query in detail in different article but not here.
First check below example to print number 1 to 10 with help of CONNECT BY LEVEL. This is the common question which is asked during interview to print number 1 to 10 from dual.


Above query return number 1 to 10.   
We will use this method to solve given problem.


Method 1 :

SELECT&nbsp; tmp.text || '&nbsp;&nbsp; Value&nbsp;&nbsp; is&nbsp;&nbsp; ' || b.L&nbsp; FROM tmp,<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT&nbsp; LEVEL L FROM dual<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONNECT BY level &lt;= (SELECT&nbsp; max (max) FROM tmp )<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) b<br />
WHERE&nbsp;&nbsp; b.L &gt;= min<br />
AND&nbsp;&nbsp;&nbsp;&nbsp; b.L &lt;= max<br />

In above query, line no 2, 3 will gives number series 1 to 28. and I am joining this with original table.

Method 2:    This is same query as above ,just I am writing in different way.

WITH&nbsp;&nbsp;cnt AS (<br />
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT&nbsp; LEVEL L FROM&nbsp;&nbsp;&nbsp; dual<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONNECT BY level &lt;=&nbsp; (SELECT&nbsp; max (max) FROM tmp)<br />
&nbsp; &nbsp; )<br />
SELECT&nbsp; tmp.text || '&nbsp;&nbsp; Value&nbsp;&nbsp; is&nbsp;&nbsp; ' || cnt.L<br />
FROM&nbsp;&nbsp;&nbsp; tmp , cnt<br />
WHERE&nbsp;&nbsp; tmp.min &lt;= cnt.L<br />
AND&nbsp;&nbsp;&nbsp;&nbsp; tmp.max &gt;= cnt.L<br />

Method 3:  

SELECT text || ' &nbsp; Value &nbsp; is &nbsp; ' || L<br />
FROM (<br />
SELECT distinct tmp.text, level L, tmp.min<br />
from tmp<br />
connect by level &lt;= tmp.max<br />
)<br />
WHERE L &gt;= min<br />
order by text , L​

Friends, please share your knowledge as well, if you know any other method.

Follow me at : Facebook

Comments are closed.