Duplicate SELECT query result DYNAMICALLY

Question

This is my query:

SELECT Barcode 
FROM Table_Barcode
WHERE IdArticle = 'Ar-1029344'

The result is something like this:

BarCode
-------
5142589
0123454
1111145

I want to duplicate each register, for example, 4 times to be like this:

BarCode
-------
5142589
5142589
5142589
5142589
0123454
0123454
0123454
0123454
1111145
1111145
1111145
1111145

EDIT

I need to be dynamically, because in the future I dont know if I need to duplicate the registers for 4 times or 10 or 25

Answer 1

Use CROSS JOIN:

SqlFiddleDemo

SELECT t.Barcode
FROM Table_Barcode t
CROSS JOIN (VALUES (1), (2), (3), (4)) AS tab(col)
WHERE t.IdArticle = 'Ar-1029344'

Second version with variable repetition:

DECLARE @rep INT = 5;  /* How many times should be repated */

WITH cte AS
(
   SELECT TOP (@rep)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   FROM sys.All_Columns ac1         /* You can use any table to populate */
   CROSS JOIN sys.ALL_Columns ac2   /* You can use any table to populate */
)
SELECT t.BarCode
FROM TABLE_BARCODE t
CROSS JOIN cte
ORDER BY t.BarCode;

Or if you know max repetition number you can hardcode values like:

DECLARE @rep INT = 5;  /* How many times should be repated */

WITH cte AS
(
   SELECT TOP(@rep) col
   FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
                (11), (12), (13), (14), (15), (16), (17), (18), (19), (20)
                (21), (22), (23), (24), (25)) AS tab(col)
)
SELECT t.BarCode
FROM TABLE_BARCODE t
CROSS JOIN cte;

Answer 2

If you just want each row to appear four times (for whatever reason), you can do something like:

select Barcode
from Table_Barcode
cross join (select 1 union all select 2 union all
            select 3 union all select 4) Num(n)
where IdArticle = 'Ar-1029344'

For more complex queries, you might want to consider adding a numbers table to your database - which is just a table containing each integer value. This then allows you to write queries like this in the future without having to manually type out the numbers you want - you just JOIN to the numbers table and use the ON or WHERE clauses to filter it suitably.