为总结答案创建列

Question

所以我试图将我的行信息移动到列中-我以前没有使用过Pivot，现在尝试使用它，但显然我做错了- -;

这是我的原始查询

(Select
    CASE WHEN ee.ExpenseTypeID=1 THEN Sum(Amount)
    WHEN ee.ExpenseTypeID=2 THEN Sum(Amount)
    WHEN ee.ExpenseTypeID=3 THEN Sum(Amount)
    WHEN ee.ExpenseTypeID=4 THEN Sum(Amount) END as Amount,
    et.ExpenseDescription,
    ee.UserID
    From ExpensesEntries ee, ExpenseTypes et
    Where ee.ExpenseTypeID=et.ExpenseTypeID
    Group By ee.ExpenseTypeID, et.ExpenseDescription, ee.UserID
    Order By UserID) b

产生了这样的东西

Amount    | ExpenseDescription | UserID
----------------------------------------
156.00    | Upload             | 123
----------------------------------------
23.00     | Parking            | 123
----------------------------------------
15.37     | Other              | 123
----------------------------------------
112.00    | Other              | 456
----------------------------------------
28.50     | Parking            | 456
---------------------------------------- 

我想做什么

UserID | Upload | Parking | Other 
----------------------------------
123    | 156.00 | 23.00   | 15.37
----------------------------------
456    | NULL   | 28.50   | 112.00

我试着这样做-这相当于拍打一个Pivot，但在我原来的选择的情况下-我不确定我是否需要完全摆脱它们，并将其添加到Pivot ?

Select 
    CASE WHEN ee.ExpenseTypeID=1 THEN Sum(Amount)
    WHEN ee.ExpenseTypeID=2 THEN Sum(Amount)
    WHEN ee.ExpenseTypeID=3 THEN Sum(Amount)
    WHEN ee.ExpenseTypeID=4 THEN Sum(Amount) END as Amount,
    et.ExpenseDescription,
    ee.UserID
    From ExpensesEntries ee, ExpenseTypes et
    Where ee.ExpenseTypeID=et.ExpenseTypeID
    Group By ee.ExpenseTypeID, et.ExpenseDescription, ee.UserID
    Order By UserID
    PIVOT
    (Amount
    FOR et.ExpenseDescription IN ('Upload','Other','Parking')
    ) as pvt

Answer 1

您没有提供每个ExpenseTypeId值与什么相关的详细信息，但是您可以使用带有CASE表达式的聚合函数来实现这一点。CASE表达式将检查ExpenseTypeId的值并获得金额的总和-然后使用ExpenseDescription:

命名列

select ee.UserId,
    sum(case when ee.ExpenseTypeID=1 then Amount else 0 end) Upload,
    sum(case when ee.ExpenseTypeID=2 then Amount else 0 end) Parking,
    sum(case when ee.ExpenseTypeID=3 then Amount else 0 end) Other
from ExpensesEntries ee
inner join ExpenseTypes et
    on ee.ExpenseTypeID=et.ExpenseTypeID
group by ee.UserId;

如果您想使用PIVOT，那么您将修改您的查询，如下所示。这在子查询中使用两个表之间的连接，并返回UserId, ExpenseDescription和Amount。PIVOT将对每个ExpenseDescription的金额求和—此过程将描述转换为列:

select UserId, Upload, Other, Parking
from
(
    select ee.UserId,
        et.ExpenseDescription 
        Amount
    from ExpensesEntries ee
    inner join ExpenseTypes et
        on ee.ExpenseTypeID=et.ExpenseTypeID
) d
pivot
(
    sum(Amount)
    for ExpenseDescription in (Upload, Other, Parking)
) p;