T-SQL puzzle: pass each row of a table as an input to an Inline Function and develop a new stacked dataset using UNION ALL

Question

I have a unique and rare situation in T-SQL. This seems to be sort of a puzzle.

1) I have (an input) dataset, say X, with 7 rows and just one column called YearMonth:

DataSet X:

YearMonth    
2018-08    
2018-09    
2018-10    
2018-11    
2018-12    
2019-01    
2019-02

Essentially the YearMonth represents months between Aug 2018 to Feb 2019.

2) I have an Inline User Defined Function (Inline Function) called dbo.fn_zzwwhh:

This Inline Function returns a table with about 10 columns and two rows, taking an input such as 2018-08. The Inline Function is called in the following manner:

SELECT * FROM dbo.fn_zzwwhh (@YearMonth)

Essentially this Inline Function dbo.fn_zzwwhh takes a YearMonth value, say 2018-08 and returns two rows and ten columns. It always returns two rows and ten columns even for other inputs such as 2018-09, 2018-10, 2018-11, 2018-12, 2019-01, 2019-02.

Now, my situation is this:

I need to develop a new dataset, say dataset Z, with the following output:

SELECT * FROM dbo.fn_zzwwhh ('2018-08')    
UNION ALL    
SELECT * FROM dbo.fn_zzwwhh ('2018-09')    
UNION ALL    
SELECT * FROM dbo.fn_zzwwhh ('2018-10')    
UNION ALL    
SELECT * FROM dbo.fn_zzwwhh ('2018-11')    
UNION ALL    
SELECT * FROM dbo.fn_zzwwhh ('2018-12')    
UNION ALL    
SELECT * FROM dbo.fn_zzwwhh ('2019-01')    
UNION ALL    
SELECT * FROM dbo.fn_zzwwhh ('2019-02')

This dataset Z is needed for my SSRS report.

Can anyone let me know on how to pass EACH row from dataset X (one at a time) into the Inline Function dbo.fn_zzwwhh, and develop the dataset Z, using UNION ALL between each passed in input value ?

I would greatly appreciate if you could provide me a solution.

Answer 1

Have you tried to use CROSS APPLY?

SELECT *
FROM (
    SELECT YearMonth
    FROM X 
) a
CROSS APPLY dbo.fn_zzwwhh(a.YearMonth)