Can I expand a dataset in SQL by adding a month column that has all the months for each category in-CodePudding

I have a table that looks like this:

category  value
----------------
A         2000
B         1000

I have been trying to create a column with every month of the year for each category in another column. So for my example, I would be looking for SQL code to give the following output.

category month    value
------------------------
A        January  2000
A        February 2000
A        March    2000
A        April    2000
...
B        January  1000
B        February 1000
B        March    1000
B        April    1000
...

Any assistance would be highly appreciated.

CodePudding user response：

You’re looking for CROSS JOIN:

WITH Month (month) AS (VALUES ('January'), ('February'), ('March'), ('April'), ('May'), ('June'), ('July'), ('August'), ('September'), ('October'), ('November'), ('December'))
SELECT a.category, b.month, a.value
FROM Category a
CROSS JOIN Month b