如何将一行中的字段转换为列? [英] How do I convert the fields in one row into columns?

问题描述

我有一个表，其中包含诸如:

I have a table with columns such as:

          Cost     Rate

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

并且我希望将此数据作为列(Repair，Wear，Fuel)作为

and I want this data as columns(Repair,Wear,Fuel) as:

         Repair    Wear   Fuel
           825     2066    950

如何使用MS Access查询做到这一点?

How could I do this using an MS Access Query?

推荐答案

虽然有一个传统的SQL解决方案非常笨拙，但请阅读

While there's a traditional SQL solution for this that is pretty kludgy, reading this page alerted me to the fact that MS Access has a TRANSFORM ... PIVOT statement you probably should look into and use to do this.

我不确定，但是看起来应该像这样:

I can't be certain but it should look like:

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

它可以比这更出色.例如

And it can get fancier than this. E.G.

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

这篇关于如何将一行中的字段转换为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！