How to Retrieve Rows Multiple Times in SQL Server

Select Records multiple times from table

SELECT Location 
FROM Table1
  CROSS JOIN
    ( VALUES (1),(2),(3),(4)
    ) AS four(dummy)

If the 4 is not a constant but (as @xQbert noticed/asked) is the number of rows of the table, you can use this:

SELECT a.Location 
FROM Table1 AS a
  CROSS JOIN
     Table1 AS b

---

If you don't have Table1 but any (however complex) query, you could use this for 4 copies:

SELECT Location 
FROM (
       SELECT Location       --- complex query here
       ...                   --- inside parenthesis
     UNION 
       SELECT Country
       ...
     ) AS Table1
  CROSS JOIN
    ( VALUES (1),(2),(3),(4)
    ) AS four(dummy)

or this for n copies:

WITH cte AS
  ( SELECT Location       --- complex query here
    ...                   --- inside parenthesis
    UNION 
    SELECT Country
    ...
  )
SELECT a.Location 
FROM cte AS a
  CROSS JOIN
     cte AS b

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

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.

MySQL - How can I get a row multiple times based on a value of the row?

If you are running MySQL 8.0, you can do this with a recursive cte:

with recursive cte as (
    -- this is your anchor
    select id, name, datefrom, dateuntil from mytable
    union all
    -- expand the dataset by incrementing "datefrom" until it reaches "dateuntil"
    select id, name, datefrom + interval 1 day, dateuntil
    from cte
    where datefrom < dateuntil
)
select id, name, datefrom date from cte order by id, date

Demo on DB Fiddle:

id | name   | date      
-: | :----- | :---------
 1 | Event1 | 2019-12-01
 1 | Event1 | 2019-12-02
 1 | Event1 | 2019-12-03
 2 | Event2 | 2019-12-07
 2 | Event2 | 2019-12-08
 3 | Event3 | 2019-12-11
 3 | Event3 | 2019-12-12

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

How get a row multiple times repeated at SQL query result?

The IN operator just checks whether the value on the left matches some value on the right.
To return multiple rows, you have to either use multiple SELECTs, or create a temporary table where the value 4 appears in multiple rows:

WITH IDs_to_search(ID) AS (
    VALUES (2), (3), (4), (4), (5)
)
SELECT ID, text
FROM MyTable
JOIN IDs_to_search USING (ID);

How to return all names that appear multiple times in table

You're almost there:

select name
from student
group by name
having count(*) > 1;

HAVING is a where clause that runs after grouping is done. In it you can use things that a grouping would make available (like counts and aggregations). By grouping on the name and counting (filtering for >1, if you want two or more, not >=1 because that would include 1) you get the names you want..

This will just deliver "Jack" as a single result (in the example data from the question). If you then want all the detail, like who Jack's siblings are, you can join your grouped, filtered list of names back to the table:

select *
from 
student
INNER JOIN
(
  select name
  from student
  group by name
  having count(*) > 1
) morethanone ON morethanone.name = student.name

You can't avoid doing this "joining back" because the grouping has thrown the detail away in order to create the group. The only way to get the detail back is to take the name list the group gave you and use it to filter the original detail data again

---

Full disclosure; it's a bit of a lie to say "can't avoid doing this": SQL Server supports something called a window function, which will effectively perform a grouping in the background and join it back to the detail. Such a query would look like:

select student.*, count(*) over(partition by name) n
from student

And for a table like this:

jack, lucy
jack, tim
jane, bill
jane, fred
jane, tom
john, dave

It would produce:

jack, lucy, 2
jack, tim, 2
jane, bill, 3
jane, fred, 3
jane, tom, 3
john, dave, 1

The rows with jack would have 2 on because there are two jack rows. There are 3 janes, there is 1 john. You could then wrap all that in a subquery and filter for n > 1 which would remove john

select * 
from
(
  select student.*, count(*) over(partition by name) n
  from student
) x
where x.n > 1

If SQL Server didn't have window functions, it would look more like:

select *
from 
student
INNER JOIN
(
  select name, count(*) as n
  from student
  group by name
) x ON x.name = student.name

The COUNT(*) OVER(PARTITION BY name) is like a mini "group by name and return the count, then auto join back to the main detail using the name as key" i.e. a short form of the latter query

SQL Select to return one line multiple times based on a number within the dataset

Try like this:

select deliveries.id,
    deliveries.traderid,
    customers.name,
    deliveries.toaddressid,
    deliveries.eutransportid,
    deliveries.street,
    deliveries.city,
    deliveries.county,
    deliveries.postcode,
    delivery_custom.palletspaces,
    ectransport.ectranspdesc
INTO #MyTemp
from deliveries
INNER JOIN customers ON
deliveries.traderid = customers.id
INNER JOIN delivery_custom ON 
deliveries.id = delivery_custom.id
INNER JOIN ectransport ON
deliveries.eutransportid = ectransport.ectranspcode

;WITH CTE AS(
    SELECT id,traderid,toaddressid,county,postcode,palletspaces,1 AS LineCount 
    FROM #MyTemp
    UNION ALL
    SELECT id,traderid,toaddressid,county,postcode,palletspaces,LineCount+1 
    FROM CTE
    WHERE LineCount<palletspaces
    )
SELECT * 
FROM CTE
ORDER BY id, LineCount;

DROP TABLE #MyTemp

Hope this time you get it.

---

Related Topics