CODEWITHSUNDEEP
sqlsql-servertranspose
Wai Wong
Wai Wong

Reputation: 2843

Transposing from rows into columns

I have a table like this:

Name    State       Amount
------------------------------
Pump 1  Present     339
Pump 1  Optimized   88

I want to transpose it to something like this:

Pump 1  Present     339 Optimized   88

How can I do this with MS SQL 2000? I tried to search for a solution, but couldn't find the most fitting one.

Upvotes: 2

Views: 837

Answers (1)

Madhivanan
Madhivanan

Reputation: 13700

declare @t table(Name    varchar(10), State       varchar(10), Amount int)
insert into @t
select 'Pump 1',  'Present',     339  union all
select 'Pump 1',  'Optimized',   88 

select name,
max(case when state='Present' then 'Present' end),
max(case when state='Present' then Amount end),
max(case when state='Optimized' then 'Optimized' end),
max(case when state='Optimized' then Amount end)
from @t
group by name

Upvotes: 1

Related Questions