Regarding View Creation

Question

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 15-Aug-13 19:47pm

Mahesh Devikar

Updated 15-Aug-13 22:12pm

Maciej Los

v2

Add a Solution

Comments

Mehdi Gholam 16-Aug-13 4:15am

Why?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-08-15T22:22:00

Solution 1

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

Posted 15-Aug-13 22:22pm

Maciej Los

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.

Maciej Los 16-Aug-13 6:51am

http://stackoverflow.com/questions/4659803/recursion-in-oracle[^]