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

This is an easy query using the PIVOT function. If you have a few values that are known to be converted to columns, then you can use a static version:

create table yourtable
(
  cost varchar(10),
  rate int
);

insert into yourtable values
('Repair', 12),
('Repair', 223),
('Wear', 1000),
('Wear', 666),
('Fuel', 500),
('Repair', 600),
('Fuel', 450),
('Wear', 400);

select *
from
(
  select cost, rate
  from yourtable
) x
pivot
(
  sum(rate)
  for cost in ([Repair], [Wear], [Fuel])
) p;

If you have an unknown number of values, then you can use dynamic SQL to create a dynamic pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(cost) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ' + @cols + ' from 
             (
              select cost, rate
              from yourtable
            ) x
            pivot 
            (
                sum(rate)
                for cost in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo. Both will produce the same results.

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.