例如，我有两张table

DECLARE @Employee TABLE( 
   [EmployeeID] INT NOT NULL, 
   [FirstName] VARCHAR(250) NOT NULL, 
   [LastName] VARCHAR(250) NOT NULL,
   [Value] INT NOT NULL,
   [ExpenditureID] INT NOT NULL
);
DECLARE @Expenditure TABLE( 
   [ExpenditureID] INT NOT NULL, 
   [Type] VARCHAR(250) NOT NULL   
);
INSERT @Expenditure ([ExpenditureID], [Type])
VALUES (1, N'Salary' ),
(2, N'Bonus')
INSERT @Employee([EmployeeID], [FirstName], [LastName], [Value], [ExpenditureID]) 
VALUES (1, N'Orlando', N'Gee', 1500, 1 ),
(2, N'Keith', N'Harris', 1000, 1),
(3, N'Keith', N'Harris', 700, 2),
(4, N'Donna', N'Carreras',2000, 1 ),
(5, N'Janet', N'Gates', 900, 1 )

我想得到这样的结果

FirstName | LastName | Value | Type
-----------------------------------
Orlando     Gee        0       Bonus
Orlando     Gee        1500    Salary
Keith       Harris     700     Bonus
Keith       Harris     1000    Salary
Donna       Carreras   0       Bonus
Donna       Carreras   2000    Salary
Janet       Gates      0       Bonus
Janet       Gates      900     Salary

我编写了返回相同结果的查询

SELECT p.FirstName, p.LastName, SUM(p.Value) AS Value, p.Type FROM (
    SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.Value, exp.Type FROM @Employee AS emp
        INNER JOIN @Expenditure AS exp ON exp.ExpenditureID = emp.ExpenditureID 
    UNION ALL
    SELECT emp.EmployeeID, emp.FirstName, emp.LastName, 0, exp.Type FROM @Employee AS emp
        CROSS JOIN @Expenditure AS exp
    WHERE exp.ExpenditureID <> emp.ExpenditureID
) AS p
GROUP BY p.FirstName, p.LastName, p.Type

但我不喜欢这样。有没有其他更有效的解决方案？