Select Records multiple times from table

问题:

I have a SQL statement like this:

EDIT :

SELECT 
    location as Location 
FROM
    Table1 
WHERE 
    OnsiteOffshore = 'Offshore' AND Acc_Code = 'ABC' 

UNION 

SELECT 
    Country 
FROM 
    Table1 
WHERE
    OnsiteOffshore = 'Onsite' AND Acc_Code = 'ABC'

This SQL query gives these results:

Chennai
Bangalore
USA
NewZealand

But due to some requirement I need the output like this:

Chennai
Chennai
Chennai
Chennai
Bangalore
Bangalore
Bangalore
Bangalore
USA
USA
USA
USA
NewZealand
NewZealand
NewZealand
NewZealand

Mean to say each location needs to be output 4 times.

Pls help how to get the same.

回答1:

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

回答2:

Simplest and (probably) fully accepted in any RDBMS ;-):

select location from (
    Select Location From Table1 union all
    Select Location From Table1 union all
    Select Location From Table1 union all
    Select Location From Table1
) t
order by location

And better way with CTE (Common Table Expressions):

;with cte (id) as (
    select 1 union all
    select id + 1 from cte where id < 4
)
select location from Table1
cross join cte

回答3:

You can create a wrapper procedure for this... where u first create a cursor for Select Location From Table1 than you can loop through this cursor and extract the data whatever way want.

optionally you can use some front end technology to do this. This is not a tough task in front end languages i.e. Java, C++, .NET or in any other popular language.

回答4:

DECLARE @counter int
DECLARE @max int = 4

CREATE TABLE #myTable (
    Name nvarchar(50)
)

SET @counter = 0

WHILE @counter < @max

BEGIN
    INSERT INTO #myTable SELECT cityname FROM citytable

    SET @counter = @counter + 1
END


SELECT name FROM #myTable ORDER BY name

DROP TABLE #myTable

回答5:

Will only work if Table1 has at least 4 records

SELECT Location
FROM Table1 a, (select top 4 id from Table1) b 
order by a.Location

回答6:

8-)

Select Location From Table1 UNION ALL
Select Location From Table1 UNION ALL
Select Location From Table1 UNION ALL
Select Location From Table1 
ORDER BY 1

回答7:

select t1.location 
from table1 t1, table1 t2

should return n locations of n. so if locations contain 10 entries, one of which is USA, USA would be listed 10 times. This scales depending on the number of locations entered.

or a more modern syntax:

Select t1.location
from table1 t1
Cross join table1 t2

notice the lack of an ON clause; that's because we want the Cartesian product of the self join.

--EDIT reflecting changed SQL

Select A.Location 
FROM (
  Select  location as Location 
  from Table1 where OnsiteOffshore = 'Offshore' and Acc_Code = 'ABC' 
  UNION 
  Select  Country 
  from Table1 where OnsiteOffshore = 'Onsite' and Acc_Code = 'ABC') A
CROSS JOIN (
  Select  location as Location 
  from Table1 where OnsiteOffshore = 'Offshore' and Acc_Code = 'ABC' 
  UNION 
  Select  Country 
  from Table1 where OnsiteOffshore = 'Onsite' and Acc_Code = 'ABC') B