How to convert fields in one row to columns?

Question

I have a table with columns, for example:

          Cost     Rate

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

and I want this data to be columns (Repair, Wear, Fuel) like:

         Repair    Wear   Fuel
           825     2066    950

How can I do this using an MS Access request?

Answer 1

Despite the fact that the traditional SQL solution for this is quite fun, reading this page warned me that MS Access has TRANSFORM ... PIVOT , which you should probably learn and use to do this.

I cannot be sure, but it should look like this:

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

And it can become more attractive than that. For example.

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

Answer 2

unverified but

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

otherwise, if there is any other database engine, you can do a quick statement right before that

create table dual (x char)

insert into dual values "x"

Answer 4

You can do this with a Crosstab query. There is a wizard in the "Access Requests" window that helps you create it. Just click the “New Request” button and select the Crosstab Request Wizard.

Answer 5

This will provide the necessary data:

select cost, sum(rate)

from mytable

group by cost

and then you can imagine it no matter what you like.