MS SQL how to select n rows from table even if table has n - x rows

Question

I have a table with a following data:

ID | Name
---------
1  | John
2  | Alice

And when I want to select 5 rows I want to get the next data:

ID | Name
---------
1  | John
2  | Alice
1  | John
2  | Alice
1  | John

Is there any ideas how to make this?

You could UNION ALL the table with itself, but why would you want to do that in SQL? I assume you get the data then you process it with code. There is no reason to get more data than you need from the database. You can display or process it in code later. — Siderite Zackwehdex, Apr 28 '16 at 10:17
Possible duplicate of [SQL: Repeat a result row multiple times, and number the rows](http://stackoverflow.com/questions/10423767/sql-repeat-a-result-row-multiple-times-and-number-the-rows) — Yahya, Apr 28 '16 at 10:19

gofr1 · Answer 1 · 2016-04-28T11:01:22.870

Works for n > 1 rows:

;WITH cte AS (
SELECT *
FROM (VALUES
(1, 'John'),
(2, 'Alice')
) AS t(ID, Name)
)
,res AS (
    SELECT  id,
            name,
            ROW_NUMBER() OVER (partition by id ORDER BY ID) as pid
    FROM cte
    UNION ALL
    SELECT  c.id,
            c.name,
            pid + 1
    FROM res r
    INNER JOIN cte c 
        ON pid = c.id 
)

SELECT TOP 5 
        id,
        name
FROM res

Output:

id          name
----------- -----
1           John
2           Alice
1           John
2           Alice
1           John

(5 row(s) affected)

score 2 · Accepted Answer · answered Apr 28 '16 at 11:14

-- to create a sample table.
CREATE TABLE table1 (ID BIGINT, NAME VARCHAR(255))
INSERT INTO table1 VALUES (1, 'John'), (2, 'Alice')

-- set number of rows you need
DECLARE @RowsReqd INT = 5  

-- find out the max ID we want to generate 
DECLARE @Limit INT
SELECT @Limit = MAX(ID) FROM table1

-- generate the list
;WITH NumbersList
AS (
    SELECT 1 AS Number, 1 AS ID
    UNION ALL
    SELECT Number + 1, Number % @Limit + 1 FROM NumbersList
    WHERE Number < @RowsReqd    
    )
SELECT T.*
FROM NumbersList NL
INNER JOIN table1 T ON T.ID = NL.ID
ORDER BY NL.Number
OPTION (MAXRECURSION 10000) -- increase this value to generate more numbers

OUTPUT:

ID   NAME
1    John
2    Alice
1    John
2    Alice
1    John

MS SQL how to select n rows from table even if table has n - x rows

2 Answers2