Clever Geek Handbook

Selecting a rowset more than once

Is there a simple and concise way to select the same set of rows repeated based on the count stored in a variable, without using a loop?

For example, suppose it SELECT a, b, c FROM foo WHERE whatsit = something

returns

abc
--- --- ----
1 2 3

... and I am @count

with 3

in it. Is there a sane way without a loop:

abc
--- --- ----
1 2 3
1 2 3
1 2 3

? The order doesn't matter, and I don't need to know which group a given row belongs to. I actually only need this for one line (as above) and a solution that only works for one line will do the trick, but I guess if we can do it for one we can do it for any number ...

+3


source to share


3 answers


Try with Recursive CTE 



WITH cte
     AS (SELECT 1 AS id,a,b,c
         FROM   tablename
         UNION ALL
         SELECT id + 1,a,b,c
         FROM   cte
         WHERE  id < 3) --@count
SELECT a,b,c
FROM   cte
+5


source


Another way to do it with cross join 



SELECT a, b, c
FROM Table1
CROSS JOIN (SELECT number
            FROM master.dbo.spt_values
            WHERE type = 'P'
            AND number BETWEEN 1 AND 3) T
0


source


I don't know how you could do this without a loop or dynamic SQL. I think union is all I can think of

select q1.a,q1.b,q1.c
from (
    SELECT a, b, c FROM foo 
    union all
    SELECT a, b, c FROM foo 
    union all
    SELECT a, b, c FROM foo ) q1
order by q1.a
-2


source






More articles:


All Articles