SQL - For each ID, values in other columns should be repeated

Question

The table I am trying to create should look like this

**ID** **Timeframe** Value
1            60        15
1            60        30
1            90        45
2            60        15
2            60        30
2            90        45
3            60        15
3            60        30
3            90        45

So for each ID the values of 60,60,90 and 15,30,45 should be repeated. Could anyone help me with a code? :)

Answer 1

You are looking for a cross join. The basic idea is something like this:

select i.id, tv.timeframe, tv.value
from (values (1), (2), (3)) i(id) cross join
     (values (60, 15), (60, 30), (90, 45)) tv(timeframe, value)
order by i.id, tv.value;

Not all databases support the values() table constructor. In those databases, you would need to use the appropriate syntax.

Answer 2

So you have this table: ...

id
 1 
 2
 3

and you have this table: ...

timeframe  value
       60     15  
       60     30  
       90     45  

Then try this:

WITH
-- the ID table...
id(id) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
)   
,   
-- the values table:
vals(timeframe,value) AS (
          SELECT 60,15
UNION ALL SELECT 60,30
UNION ALL SELECT 90,45
)   
SELECT
  id  
, timeframe
, value
FROM id CROSS JOIN vals
ORDER BY id, timeframe;
-- out  id | timeframe | value 
-- out ----+-----------+-------
-- out   1 |        60 |    30
-- out   1 |        60 |    15
-- out   1 |        90 |    45
-- out   2 |        60 |    30
-- out   2 |        60 |    15
-- out   2 |        90 |    45
-- out   3 |        60 |    30
-- out   3 |        60 |    15
-- out   3 |        90 |    45
-- out (9 rows)