如何转换领域一行到列？(How do I convert the fields in one row

我有诸如列的表：

          Cost     Rate

          Repair   12
          Repair   223
          Wear     1000    
          Wear     666
          Fuel     500
          Repair   600
          Fuel     450
          Wear     400

我想这个数据列（修理，磨损，燃油）为：

         Repair    Wear   Fuel
           825     2066    950

我怎么可以这样使用MS Access查询？

Answer 1:

虽然有这个传统的SQL解决方案，这是非常缺憾，阅读此页提醒我的事实，MS Access有一个变换... PIVOT你应该考虑和使用要做到这一点的语句。

我不能肯定，但它应该是这样的：

TRANSFORM Sum([Items].[Rate]) AS SumOfRate 
SELECT [Items].[Costs] 
FROM Items 
GROUP BY [Items].[Costs] 
PIVOT Format([Items].[Costs]);

它可以比这个票友。 例如

PIVOT Format([Items].[month],"mmm") In ("Jan","Feb",...,"Nov","Dec");

Answer 2:

但未经测试

SELECT
(SELECT  sum(rate)  from mytable where cost = 'Repair') AS Repair ,
(SELECT  sum(rate) from mytable where cost = 'Wear') AS Wear,
(SELECT  sum(rate) from mytable wherecost = 'Fuel') AS Fuel

Answer 3:

您可以使用此查询

select 
sum(select rate from yourtable where cost = 'Repair') "Repair", 
sum(select rate from yourtable where cost = 'Wear') "Wear", 
sum(select rate from yourtable where cost = 'Fuel') "Fuel" 
from dual

如果您使用的是Oracle

否则，如果任何其他数据库引擎，你可以做一个快速的声明中一个正确的前

create table dual (x char)

insert into dual values "x"

Answer 4:

您可以使用交叉表查询做到这一点。 存在这样会引导您完成创建一个访问查询窗口的向导。 只需单击新建查询按钮，然后选择交叉表查询向导。

Answer 5:

这将让你需要的数据：

select cost, sum(rate)

from mytable

group by cost

然后你可以提出它你最喜欢的方式。

文章来源: How do I convert the fields in one row into columns?