Make SQL Select Same Row Multiple Times

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



Leave a reply



Submit