August 11, 2011 at 5:18 pm
I'm beating my brains over the simplest query. Here's a similar one to what I'm trying to do:
Say I have a table with two columns, Person and Fruit
Person Fruit
Bob Orange
Dick Apple
Sally Apple
Meg Orange
Bob Orange
Meg Apple
Meg Apple
Sally Apple
Bob Apple
All I want to show is for each user, how many Apples and Oranges did they eat
Person Apples Oranges
Bob 2 2
Meg 2 1
And I can't figure out how to do this with sets, I did find one way to do it with Count(*) and GROUP BY, but it gave me two rows per user for each fruit with a Count column, so not the result set I need.
SELECT DISTINCT Fruit, Person, Count(*) FROM #TEMPTABLE
GROUP BY Fruit, Person
ORDER BY Person
Any way to get the result set per user without getting into cursors?
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 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply