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], Price
FROM cte
ORDER 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.Price
FROM Table1 t CROSS APPLY (
SELECT TOP(t.[Order]) N
FROM E16) ca
ORDER 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
AS
RETURN
(
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.GeneratedDate
FROM
YourTable AS T
INNER JOIN GeneratedCalendar AS G ON
T.YEAR = G.Year AND
T.MONTH = G.Month
ORDER BY
T.YEAR,
T.MONTH
OPTION
(MAXRECURSION 30000)
Repeat each value n times as rows in SQL
Try this:
select * from names
cross join (select rownum n from dual
connect by level <= (select max(repeat) from names))
where n <= repeat
order by name
Find values repeated exactly N times in column
You could use Group by
+ Having
clause:
SELECT value1, COUNT(value1) AS value1_count
FROM table1
GROUP BY value1
HAVING 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 3
9 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 bycolumn_value
(think of it as of alevel
pseudocolumn, if it is closer to you) and thea
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)
Related Topics
How to Retrieve Microseconds or Milliseconds from MySQL Current Time
Subtract Two Columns from Two Tables With Group By
Convert String to Date in Ms Access Query
Find Out Where MySQL Is Installed on MAC Os X
Check If a Row Exists, Otherwise Insert
Sql Query for Values Consisting of Only a Specific Character
Sql Select Last N Rows, Sort Them Reversed
Sql String: Counting Words Inside a String
Combining Multiple Rows in SQL Server into One
How to Change Date Format in .Net Rdlc Report
Convert a 12 Hour Format to 24 Hour Format in SQL Server
Loop Through Table Rows and Call Stored Procedure on Every Row
How to Delete Multiple Rows in Diferent Tables
Sql - Finding Students Taking 2 or More Classes
Mysql Error 1449: the User Specified as a Definer Does Not Exist
Trim Whitespaces (New Line and Tab Space) in a String in Oracle