Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - 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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...