CODEWITHSUNDEEP
sql-server-2005pivot
Suresh Rao
Suresh Rao

Reputation:

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

Upvotes: 2

Views: 3527

Answers (7)

Taryn
Taryn

Reputation: 247690

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.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180788

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.

Upvotes: 0

Christian C. Salvadó
Christian C. Salvadó

Reputation: 827366

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

Check these articles:

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 881655

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

Upvotes: 1

glasnt
glasnt

Reputation: 2973

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

Upvotes: 4

Prashanth
Prashanth

Reputation: 2434

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

Upvotes: 0

John Kugelman
John Kugelman

Reputation: 361605

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

Upvotes: 0

Related Questions