Select same row multiple times
Why do you need this? One way I am thinking of is to have number table with 100 rows (numbers from 1 to 100) and cross join with the original query
select t1.* from
(
select A.a, B.b, C.c from A
inner join B ..
inner join C ..
where A.a =.. and B.b = .. and C.c = ..
) as t1 inner join number_table on 1=1
where number<=3
SQL Server - List single row multiple times in query result
You can try to use recursive CTE
;with cte as (
SELECT id,name,type,1 startnum,num
FROM T
UNION ALL
SELECT id,name,type , startnum+1,num
FROM cte
WHERE startnum+1<=num
)
SELECT id,name,type
FROM cte
order by id
sqlfiddle
How to select a single row multiple times in PostgreSql
You can cross join against generate_series(1,4)
, which will return a table containing the numbers 1 to 4:
SELECT mytable.*
FROM mytable
CROSS JOIN generate_series(1,4) as x
WHERE id=1
For each row in your original result set, there will be one copy with 1 next to it, one with 2, and so on.
Selecting the same row multiple times
;with C as
(
select ChildID,
ParentID,
Occurences - 1 as Occurences
from @Children
union all
select ChildID,
ParentID,
Occurences - 1 as Occurences
from C
where Occurences > 0
)
select row_number() over(order by ChildID) as IDENT,
ChildID,
ParentID
from C
order by IDENT
SQL Server: selecting a row multiple times by the given factor
This should be enough:
SELECT A.*
FROM dbo.YourTable A
INNER JOIN (SELECT *
FROM master.dbo.spt_values
WHERE type = 'P') B
ON A.Factor >= B.number+1
Here is a sqfiddle with a demo of it.
And the results are:
╔════╦════════╦═══════╗
║ ID ║ Factor ║ Count ║
╠════╬════════╬═══════╣
║ 1 ║ 1 ║ 235 ║
║ 2 ║ 2 ║ 345 ║
║ 2 ║ 2 ║ 345 ║
║ 3 ║ 2 ║ 214 ║
║ 3 ║ 2 ║ 214 ║
║ 4 ║ 3 ║ 95 ║
║ 4 ║ 3 ║ 95 ║
║ 4 ║ 3 ║ 95 ║
║ 5 ║ 1 ║ 135 ║
║ 6 ║ 1 ║ 750 ║
╚════╩════════╩═══════╝
If the factor
column can be greater than 2048, then you can use a numbers table.
Is there a way to add the same row multiple times with different ids into a table with postgresql?
Use the function generate_series()
, e.g.:
insert into my_table
select id, 'alfa', 'beta'
from generate_series(1,4) as id;
Test it in db<>fiddle.
Related Topics
How to Update Millions of Records in MySQL
How to Select Rows With No Matching Entry in Another Table
Sql Get Parent Where Children Have Specific Values
Combine Multiple Columns from Database into One Column
How to Remove Special Characters in Column With MySQL
How to Select Rows With Only Numeric Characters in Oracle SQL
State Wise Data of Country and City
How to Import a Large (14 Gb) MySQL Dump File into a New MySQL Database
Explode and Implode Strings in MySQL Query
How to Get the Month and Day With Leading 0'S in SQL (E.G. 9 => 09)
How to Limit Results of a Left Join
Deleting a Specific Character in a String from a Pattern
How to Modify Query to Remove Double-Quotes from All Columns
Truncate Seconds and Milliseconds in SQL
Sql: How to Get Both Match and Non-Match Records
Subtract Two Columns from Two Tables With Group By