SQL Select query to include each row a number of times This is probably easy to do (I hope), but my experiments with OUTER JOIN and CROSS JOIN etc do not give the results I am after, so here's the question:

I need to do do a projection on fields in a table for each day of the week, where the day of the week represents a certain weight. I assumed a number table would help, but how?

Input table:

Key  prc   fixweight
1   23.4   0.2
2   78.1   0.6
3    8.7   0.3


Required output (left out: weight calculation on the columns, based on the day of the week):

1   23.4   0.2   1
1   23.4   0.2   2
1   23.4   0.2   3
1   23.4   0.2   4
1   23.4   0.2   5
1   23.4   0.2   6
1   23.4   0.2   7
2   78.1   0.6   1
2   78.1   0.6   2
2   78.1   0.6   3
2   78.1   0.6   4
2   78.1   0.6   5
2   78.1   0.6   6
2   78.1   0.6   7
3    8.7   0.3   1
3    8.7   0.3   2
3    8.7   0.3   3
3    8.7   0.3   4
3    8.7   0.3   5
3    8.7   0.3   6
3    8.7   0.3   7
Silly, I just misinterpreted the results by using wrong ordering. The CROSS JOIN was indeed the correct approach. Here's what I did (please do correct me if this is not the proper approach to take):

WITH week(day) AS 
(
  SELECT TOP (7) number + 1 
  FROM [master].dbo.spt_values
  WHERE [type] = N'P' ORDER BY number
)

SELECT key, prc, fixweight, week.day
  FROM weights
  CROSS JOIN week
  ORDER BY key


As have been suggested by the comments: use spt_values only as a quick hack for ad-hoc queries or testing, in production you should use a number table or, if the list of numbers is so slow, you can use ypercube's solution:

SELECT key, prc, fixweight, week.day
  FROM weights
  CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7)) AS week(day)
  ORDER BY key