Rows to Columns using Sql Query

Question

hi i have a table with columns as

              Cost     Rate

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

Now i want this data as

             Repair    Wear   Fuel
               825     2066    950

Using Sql Query

Thanks in advance

Answer 1

You can use GROUP BY to combine results when using an aggregate function like SUM().

SELECT Cost, SUM(Rate) FROM MyTable GROUP BY Cost

This will return the results inverted from what you requested, but that shouldn't be a big deal I hope.

Cost     SUM(Rate)
---      ---
Repair   825
Wear     2066
Fuel     950

Answer 2

select sum(case when cost = 'Repair' then rate else null end) as Repair
, sum(case when cost = 'Wear' then rate else null end) as Wear
, sum(case when cost = 'Fuel' then rate else null end) as Fuel 
from CostRateTable

Answer 3

select cost,sum(rate) from tablename group by cost

Answer 4

The "rotation" you want (making rows into columns) can be obtained with SQL Server's PIVOT operator.

Answer 5

I think that you're looking for a way to do Dynamic Pivoting or Dynamic Cross-Tabs.

Check these articles:

• SQLServer: Dynamic Cross-Tab
• Dynamic Cross-Tabs/Pivot Tables
• Crosstab Pivot-table Workbench
• SQL Dynamic CrossTab Stored Procedure

Answer 6

You can do this with a simple Crosstab query. There is a wizard in the Access Queries window that will walk you through creating one. Just click the New Query button and select the Crosstab Query Wizard.