提问

现有一表ee，数据为:
employee_sn   vehicle_type   Cvehicle_type   time_money
-----------   ------------   -------------   ---------------------
400012             1                         4                           20.00
400027             1                         13                         65.00
400012             2                         2                           20.00
400027             2                         1                           10.00
使用如下语句：
    select   *     from   ee
    pivot
    (
        sum(cvehicle_type)
        for   vehicle_type   in  
      ([0],[1],[2],[3],[4],[5],[7])
    )     as   PVT
得到如下数据结果：
employee_sn   time_money                         0                       1                       2                       3
-----------   ---------------------   -----------   -----------   -----------   -----
400027             10.00                                   NULL                 NULL                 1                       NULL
400012             20.00                                   NULL                 4                       2                       NULL
400027             65.00                                   NULL                 13                     NULL                 NULL
大家可以发现。40012的两行记录给我合并成一行了，并且Time_money字段的值没有合计，而我的目的是要分两行给我显示出来。或者合并的时候把Time_money字段给我做个合计出行。

在线等待！！

大家帮帮忙啊！

Thanks!

USE TEST
go
IF OBJECT_ID('EE') IS NOT NULL

   drop table ee;
go
create table ee(employee_sn char(8),vehicle_type int,Cvehicle_type int,
                time_money money);
               
 go
 insert ee select'400012',1,4,$20.00
 union all select'400027',1,13,$5.00
 union all select'400012',2,2,$20.00
 union all select'400027',2,1,$10.00
 go
 select * from   ee
    pivot
    (
        sum(time_money)
        for   Cvehicle_type   in  
      ([4],[13],[2],[1])
    )     as   PVT

总结

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output

看一下定义pivot还真是对对unique value操作的,由于你加入了Identity值,她影响操作,所以都会显示,假如for xxx in()xxx只要不重复,结果就没问题,另注意这列sum(time_money) ,可以是Cvehicle_type 本身做聚合函数,看要的结果

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO