关于视图创建 [英] Regarding View Creation

问题描述

我的表T1如下

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.

推荐答案

可以使用 CTE [ ^ ]。br />

It is possible to achieve using CTE[^].

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

这篇关于关于视图创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！