Click here to Skip to main content
15,922,533 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have Table T1 as follows

Col1 | Col2 | Col3 |
A    |  M1  | 3    |
B    |  M1  | 1    |
C    |  M1  | 1    |


I want Create View which display data as follows
Col1 | Col2 | 
A    |  M1  |
A    |  M1  |
A    |  M1  |
B    |  M1  | 
C    |  M1  |


I want to repeat data row in table no of time the last column data.

Can anyone help me out.

Thanks in Advance.
Posted
Updated 15-Aug-13 22:12pm
v2
Comments
Mehdi Gholam 16-Aug-13 4:15am    
Why?

1 solution

It is possible to achieve using CTE[^].
SQL
DECLARE @tmp TABLE (Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 INT)

INSERT INTO @tmp (Col1, Col2, Col3)
SELECT 'A', 'M1', 3
UNION ALL SELECT 'B', 'M1', 1
UNION ALL SELECT 'C', 'M1', 1


;WITH CTE AS
(
        --initial recordset 
	SELECT Col1, Col2, Col3, 1 AS Counter
	FROM @tmp
	WHERE Col3>=1
        --recursive part
        --add data untill counter is less or equal to Col3 ;)
	UNION ALL
	SELECT Col1, Col2, Col3, Counter + 1 AS Counter
	FROM CTE
	WHERE Counter+1<=Col3
)
SELECT *
FROM CTE
ORDER BY Col1 


Result:
A   M1  3   1
A   M1  3   2
A   M1  3   3
B   M1  1   1
C   M1  1   1
 
Share this answer
 
Comments
gvprabu 16-Aug-13 4:33am    
Nice Code my frnd....:-)
Maciej Los 16-Aug-13 5:34am    
Thank you, Gopal ;)
Adarsh chauhan 16-Aug-13 5:08am    
perfect.. +5
Maciej Los 16-Aug-13 5:35am    
Thank you ;)
Mahesh Devikar 16-Aug-13 6:31am    
Thank you for Your Reply ...........its working fine in SQL Server.

But Not in Oracle.

Any idea for oracle database same problem.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900