SQL Server: pivot

Creating a table

create table things (
   item     varchar(10),
   grp      varchar(10),
   val_one  integer,
   val_two  integer
);

Insert values

set nocount on
insert into things values('abc', 'foo',  7, 8);
insert into things values('abc', 'foo',  1, 3);

insert into things values('abc', 'bar',  2, 7);

insert into things values('abc', 'baz',  6, 2);
insert into things values('abc', 'baz', 11, 4);
insert into things values('abc', 'baz',  4, 6);

----------------------------------------------

insert into things values('def', 'foo',  4, 5);
insert into things values('def', 'foo', 11, 4);

insert into things values('def', 'bar',  9, 2);
insert into things values('def', 'bar',  5, 5);

insert into things values('def', 'baz', 12, 3);

Select with pivot operator

select
   item,
   [foo] as sum_foo,
   [bar] as sum_bar,
   [baz] as sum_baz
from (
   select
      item,
      grp,
      val_one
   from
      things
) as t
pivot (
  sum(val_one) for grp in ([foo], [bar], [baz])
) as v1;

Cleaning up

drop table things;

See also