>>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?
>
>
>

>DECLARE @Test TABLE (Id int, Amount int)
>INSERT INTO @Test VALUES (1, 1) 
>INSERT INTO @Test VALUES (1, 2) 
>
>INSERT INTO @Test VALUES (2, 3) 
>INSERT INTO @Test VALUES (2, 4) 
>
>
>;WITH CTE1 (Amount)
>AS 
>(SELECT Amount
>  FROM @Test
> WHERE ID < 2),
> 
>CTE2 (Amount)
>AS
>(SELECT Amount
>       FROM @Test
> WHERE ID  > 1)
>
>SELECT SUM(Cte1.Amount) AS Cte1Amount,
>       SUM(Cte2.Amount) AS Cte2Amount
>FROM (SELECT SUM(Amount) AS Amount FROM Cte1) Cte1,
>     (SELECT SUM(Amount) AS Amount FROM Cte2) Cte2
>

>?
>
>
>BTW why you need CTE for this?
>You can use Derived tables :-)

Thanks, it is a recursive CTE as discussed in an earlier thread a few days ago.

I have never heard of derived tables, so I guess I've got some more research to do.