>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 :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.