在视图中复制行，并为每个副本指定一个新ID

Question

我是SQL的新手，我面临着一个很容易解决的问题:

目前我的视图是这样的:

何塞。

另一种选择是CROSS JOIN引起重复和派生数字…

SELECT
src.Credittype,
src.Person,
num.Number
FROM
table      src
CROSS JOIN
(
SELECT 1 AS Number
UNION ALL SELECT 2 AS Number
UNION ALL SELECT 3 AS Number
)
num

SELECT
src.Credittype,
src.Person,
num.Number
FROM
table      src
CROSS JOIN
(
VALUES (1), (2), (3)
)
num(Number)

在SO上也有许多答案，显示了生成大范围数字的表(或内联视图)的方法。比如…

• 如何生成两个数字之间的数字范围?

这里有一个解决方案:

Declare @creditTypes Table (CreditType varchar(10), Person varchar(100));
Insert Into @creditTypes (CreditType, Person)
Values ('type 1', 'James')
, ('Type 2', 'Jose')
, ('Type 2', 'Mike');
With t(n)
As (
Select t.n
From (Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)             
)
, iTally (Number)
As (        
Select Top (50)
checksum(row_number() over(Order By @@spid))
From t t1, t t2              -- 100 rows
)
Select *
From @creditTypes                    ct
Cross Apply iTally                    it
Order By
Person
, Number;