Hi,

I need to get two different values from a single table and combine them into one result set. I obtain the values using a CTE like this in a Table-valued Function (TVF):

WITH CTE1 (Amount)
AS
(SELECT Amount
FROM MyTable WHERE MyTable.ID = @Param1)
SELECT SUM(Amount) AS Total1
FROM CTE1

Then I have the second CTE (same code but different parameter)

WITH CTE2 (Amount)
AS
(SELECT Amount
FROM MyTable WHERE MyTable.ID = @Param2)
SELECT SUM(Amount) AS Total2
FROM CTE2

How do I then get my result into one result set with the two Totals in one row?

Of course the CTE is much more complex than the example here.

Should I define the one TVF and call it twice to get the results? If so how?

Or should I make the one function that returns both totals?

Or is there another way? Maybe make this a Scalar-Valued Function?