August 11, 2011 at 7:08 pm
I think this article will help you get what you want. It seems like you need pivot to make it work.
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2011 at 7:09 pm
Here is one way to accomplish this task (which, by the way I did on SQL Server 2008 R2):
create table dbo.PersonFruit (
Person varchar(10),
Fruit varchar(10));
go
insert into dbo.PersonFruit
values
('Bob', 'Orange'),
('Dick', 'Apple'),
('Sally', 'Apple'),
('Meg', 'Orange'),
('Bob', 'Orange'),
('Meg', 'Apple'),
('Meg', 'Apple'),
('Sally', 'Apple'),
('Bob', 'Apple');
go
select * from dbo.PersonFruit;
go
select
Person,
sum(case when Fruit = 'Apple' then 1 else 0 end) as Apple,
sum(case when Fruit = 'Orange' then 1 else 0 end) as Orange
from
dbo.PersonFruit
group by
Person
order by
Person;
go
drop table dbo.PersonFruit;
go
To make this work in SQL Server 2005 you need to break down the INSERT INTO into individual statements.
August 11, 2011 at 7:26 pm
Lynn Pettis (8/11/2011)
Here is one way to accomplish this task (which, by the way I did on SQL Server 2008 R2):
create table dbo.PersonFruit (
Person varchar(10),
Fruit varchar(10));
go
insert into dbo.PersonFruit
values
('Bob', 'Orange'),
('Dick', 'Apple'),
('Sally', 'Apple'),
('Meg', 'Orange'),
('Bob', 'Orange'),
('Meg', 'Apple'),
('Meg', 'Apple'),
('Sally', 'Apple'),
('Bob', 'Apple');
go
select * from dbo.PersonFruit;
go
select
Person,
sum(case when Fruit = 'Apple' then 1 else 0 end) as Apple,
sum(case when Fruit = 'Orange' then 1 else 0 end) as Orange
from
dbo.PersonFruit
group by
Person
order by
Person;
go
drop table dbo.PersonFruit;
go
To make this work in SQL Server 2005 you need to break down the INSERT INTO into individual statements.
That works too.:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2011 at 9:20 am
That worked perfectly! I plugged it into my real data, obviously i'm not counting fruit 😉
Thank you!!
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply