Rows to Columns using Sql Query

问题:

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

回答1:

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

回答2:

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

回答3:

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

回答4:

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

回答5:

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

回答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.

回答7:

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.