>>>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.
Derived tables are not recursive.
You must use CTEs in that case :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.