﻿ Repeat Each Value in SQL Table N Times With 1:N in Different Column - ITCodar

# Repeat Each Value in SQL Table N Times With 1:N in Different Column

## Repeat Rows N Times According to Column Value

You could do that with a recursive CTE using `UNION ALL`:

``;WITH cte AS  (    SELECT * FROM Table1    UNION ALL    SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price    FROM cte INNER JOIN Table1 t      ON cte.[ID] = t.[ID]    WHERE cte.[Order] > 1)SELECT [ID], ProductFK, 1 [Order], PriceFROM cteORDER BY 1``

Here's a working SQLFiddle.

Here's a longer explanation of this technique.

Since your input is too large for this recursion, you could use an auxillary table to have "many" dummy rows and then use `SELECT TOP([Order])` for each input row (`CROSS APPLY`):

``;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),      E02(N) AS (SELECT 1 FROM E00 a, E00 b),      E04(N) AS (SELECT 1 FROM E02 a, E02 b),      E08(N) AS (SELECT 1 FROM E04 a, E04 b),      E16(N) AS (SELECT 1 FROM E08 a, E08 b)SELECT t.[ID], t.ProductFK, 1 [Order], t.PriceFROM Table1 t CROSS APPLY (  SELECT TOP(t.[Order]) N  FROM E16) caORDER BY 1``

(The auxillary table is borrowed from here, it allows up to 65536 rows per input row and can be extended if required)

Here's a working SQLFiddle.

## Repeat Rows N Times According to Column Value, Without Limit in Repeating

Finlay I found the solution. We can not use "OPTION(MAXRECURSION 0)" in CTE structure but we can use our query as a function and use "OPTION(MAXRECURSION 0)" in calling and running Function likes following:

``Create fnCreateIndex(    @Pr1 Int)RETURNS TABLE ASRETURN (WITH Numbers(Num) AS(  SELECT 1 AS Num  UNION ALL   SELECT Num + 1  FROM Numbers c  WHERE c.Num < @Pr1), CTE as( SELECT partname, qty, num FROM supplylist JOIN Numbers ON supplylist.qty >= Numbers.Num) Select * from cte)``

Finaly we can use this for getting the resuls:

`` select * from fnCreateIndex (50000)  order by partname, num OPTION(MAXRECURSION 0)``

I found solution according to: https://stackoverflow.com/a/7428903/4885037

## SQL query to repeat each row n times and add to it, data from another table

Build the result table as a union of the games played by users and the games not played by users like so:

``  select UserId, GameId, GameName, Played  from (    -- games played    select usr.UserId, gme.GameId, gme.GameName, 1 as Played    from USERS usr    inner join GAMES gme on usr.GameId = gme.GameId    union    -- games not played    select usr.UserId, gme.GameId, gme.GameName, 0 as played    from USERS usr, GAMES gme    where gme.GameId not in (      select usr2.GameId      from USERS usr2       where usr2.UserId = usr.UserId    )  )  order by Userid, Played desc, Gameid``

The outer select is used to order the results set such that games played are provided first.

## Repeating rows based on count in a different column - SQL

I would use a recursive CTE, but directly:

``with cte as (      select CID, CVID, count, 1 as ro      from #temp1      union all      select CID, CVID, count, ro + 1      from cte      where cte.ro < cte.count     )select cte.*from cte;``

If your counts exceed 100, then you'll need to use `option (maxrecursion 0)`.

## Repeat one row in SQL Server n times - according on a column

Use a calendar table, it will solve this and many future dates problems for you.

This solution generates one with a recursive CTE.

``DECLARE @StartDate DATE = '2018-01-01'DECLARE @EndDate DATE = '2020-01-01';WITH GeneratedCalendar AS(    SELECT        GeneratedDate = @StartDate,        Month = MONTH(@StartDate),        Year = YEAR(@StartDate)    UNION ALL    SELECT        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate),        Month = MONTH(DATEADD(DAY, 1, G.GeneratedDate)),        Year = YEAR(DATEADD(DAY, 1, G.GeneratedDate))    FROM        GeneratedCalendar AS G    WHERE        G.GeneratedDate < @EndDate)SELECT    T.YEAR,    T.MONTH,    T.Cost,    G.GeneratedDateFROM    YourTable AS T    INNER JOIN GeneratedCalendar AS G ON        T.YEAR = G.Year AND        T.MONTH = G.MonthORDER BY    T.YEAR,    T.MONTHOPTION    (MAXRECURSION 30000)``

## Repeat each value n times as rows in SQL

Try this:

``select * from namescross join (select rownum n from dual            connect by level <= (select max(repeat) from names))where n <= repeatorder by name``

## Find values repeated exactly N times in column

You could use `Group by` + `Having` clause:

``SELECT value1, COUNT(value1) AS value1_countFROM table1GROUP BY value1HAVING COUNT(*) = 3``

DEMO

## How to repeat the rows n times

Like this?

``SQL> with test (a, b, c) as  2    (select 1, 2, 3 from dual union all  3     select 2, 3, 4 from dual  4    ),  5  temp as  6    (select a, b, c,  7       row_number() over (order by column_value, a) rn  8     from test cross join table(cast(multiset(select level from dual  9                                              connect by level <= 9 10                                             ) as sys.odcinumberlist)) 11    ) 12  select a, b, c 13  from temp 14  where rn <= 9 15  order by rn  ;         A          B          C---------- ---------- ----------         1          2          3         2          3          4         1          2          3         2          3          4         1          2          3         2          3          4         1          2          3         2          3          4         1          2          39 rows selected.SQL>``

What does it do?

• lines #1 - 4 represent your sample data
• CTE `temp` (lines #5 - 11) created all those rows; `row_number` is used to "rank" them, ordered by `column_value` (think of it as of a `level` pseudocolumn, if it is closer to you) and the `a` column value (why? Your sample output suggests so)
• final query (lines #12 - 15) selects the result for `rn <= 9` (as you wanted to get 9 rows)