0

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
1

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
5
  • 3
    I would suggest creating your own numbers table, with only 7 values. Using master.dbo.spt_values is a quick and dirty workaround that works for testing.
    – Hannah Vernon
    May 8 '14 at 3:58
  • 1
    I recently implemented a numbers table in our database that's exposed via inline-TVF. We end up with very compact queries like SELECT Number FROM dbo.GetNumberRange(1, 7). This also works great for generating date ranges (another TVF). I put 2,048 values in the table just like master.dbo.spt_values, but obviously it's expandable if more range is needed.
    – Jon Seigel
    May 8 '14 at 13:38
  • 1
    For such a small list, you can also use ... CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7)) AS week(day) in versions 2008+ May 8 '14 at 17:21
  • @ypercube: it's a pity that the xpath to syntax is not available in SQL: CROSS JOIN (VALUES 1 TO 7) is just so much easier and clearer.
    – Abel
    May 8 '14 at 21:20
  • @JonSeigel: indeed, we found more needs for number tables and have moved to inline table-valued functions (without originally knowing there was a term for that, though) to serve the general use-case.
    – Abel
    May 8 '14 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.