Thanks Naomi,
the difficulty was dealing with two CTE's not selecting the data into the final output format. Boris' answer got me going.
>
>SELECT SUM(case when ID = @Param1 THEN Amount END) AS Total1,
>SUM(CASE WHEN ID = @Param2 THEN Amount END) AS Total2
>FROM myTable
>
>
>>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?