Create table in Microsoft SQL Server based on Distinct result set

Question

My use case is to create rows for every month based on result set of distinct values which doesn't contain a YYYYMM date attached to it.

Example distinct data result set

columns: Name, Age, Working Hours

• Miquell, 25, 160

Expected Result

columns: Name, Age, Working Hours, Date(YYYYMM) for 2 years per month (202001, 202002, 202003 etc)

Miquell, 25, 160, 202001
Miquell, 25, 160, 202002
Miquell, 25, 160, 202003

Can this be done in a easy solution? Copying the data with a timestamp and keep on adjusting and adding to a temp table when i change the month values doesn't seem very user friendly.

Thanks for the help,

Answer 1

You can use a recursive CTE that computes the date and finally join to the row(s) you have.

WITH
Recursion AS
(
SELECT CAST('2020-01-01' AS date) AS D
UNION ALL
SELECT DATEADD(MONTH, 1, D)
FROM   Recursion
WHERE  D < '2100-12-01'
)
SELECT 'Miquell' AS Name, 25 AS Age, 160 AS WorkingHours, CONVERT(CHAR(6), D, 112)
FROM   Recursion
OPTION (maxrecursion 0);