Duplicate rows in view and give each duplicate a new ID

Question

I am new to SQL and I am confronted with a hopefully easy to solve Problem:

Currently my view looks like this:

Credittype	Person
Type 1	James
Type 2	Jose
Type 2	Mike

Now I want to duplicate each row in this example 2 times, and give each copied row a new number.

The resulting table should look like this:

Credittype	Person	Number
Type 1	James	1
Type 1	James	2
Type 1	James	3
Type 2	Jose	1
Type 2	Jose	2
Type 2	Jose	3
Type 2	Mike	1
Type 2	Mike	2
Type 2	Mike	3

An easy solution would be something like:

Select Credittype, Person, "1" AS Number from table 
union all
Select Credittype, Person, "2" AS Number from table 
union all
Select Credittype, Person, "3" AS Number from table

The problem is that in reality I need to copy those rows 50 times... I think there is a more efficient way to solve this problem.

Does this answer your question? [Count rows belonging together](https://stackoverflow.com/questions/22325662/count-rows-belonging-together) — Stephan Bauer, Jul 26 '21 at 16:11
the answer will depend on database system you use (mySQL, SQL Server, PostgreSQL ...) — Игорь Тыра, Jul 26 '21 at 16:11

score 1 · Accepted Answer · answered Jul 26 '21 at 16:14

One alternative is to CROSS JOIN to cause the duplication and derived numbers...

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

Or, with SQL Server...

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

There are also numerous answers on SO which show ways of generating tables (or inline-views) of large ranges of numbers. Such as...

How to generate a range of numbers between two numbers?

score 0 · Answer 2 · answered Jul 27 '21 at 14:24

Here is one solution:

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;

Duplicate rows in view and give each duplicate a new ID

2 Answers2