Group by distinct across several rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group by distinct across several rows

Author

Topic

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-08-01 : 05:37:58

I'm wondering if anyone can help me with this problem am I'm sure it is very common. I'm trying to group objects that are unique, which would be easy if their attributes were only on one row, but the problem is they are more than one row.

For example, say I have a table of people and a table of cars and they are joined by who owns what car. This means one person can own several cars. For the sake of this example lets say a car can only have one owner. So the two tables look like this:

People:

PersonId (pk)| Name | Earnings | Other attributes...

Cars:

CarId (pk) | PersonId (fk) | CarModel (fk) | Other attributes...

Now my problem is what if I want to find out how many people own the same CarModels? So take two people:

PersonId: 1
Cars Owned (Car Id):1,2
Car 1 Model Id:1
Car 2 Model Id:4

PersonId: 2
Cars Owned (Car Id):3,4
Car 1 Model Id:1
Car 2 Model Id:4

So I would want this two people to be associated as they have the same model cars and other other people with the same models. As an out put I would want a count of the number of people with the same model cars or perhaps even another aggrigate function, say an average of their earnings which would be an attribute in the people table.

Thanks for the help!

Mike

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-08-01 : 05:54:35

Just to clarify, take these six people and the car models they own:

person1:beetle,jeep
person2:beetle,jeep
person3:beetle
person4:jeep
person5:beetle, jeep,jag
person6:beetle, jeep,jag

Id want the resulting groups that I could use aggrigate functions on to be:

Group1:Person1,Person2
Group2:Person3
Group3:Person4
Group4:Person5,Person6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-01 : 06:09:32

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-01 : 06:43:03

[code]--sample data
declare @Cars table (CarId int identity(1, 1), PersonId int, CarModelId int)
insert @Cars
select 1, 1
union all select 1, 4
union all select 2, 1
union all select 2, 4
union all select 3, 1
union all select 4, 1
union all select 4, 4
union all select 4, 5
union all select 5, 1
union all select 6, 1
union all select 7, 4
union all select 7, 5
union all select 8, 1
union all select 8, 4

--calculation
; with
a as (select *, substring((select ',' + cast(CarModelId as varchar(10)) from @Cars
where PersonId = a.PersonId for xml path('')), 2, 100) as CarModelIds from @Cars a)
, b as (select CarModelIds, substring((select distinct ',' + cast(PersonId as varchar(10))
from a where CarModelIds = b.CarModelIds for xml path('')), 2, 100) as PersonIds
from a b group by CarModelIds)
select row_number() over (order by PersonIds) as [Group], * from b

/* results
Group CarModelIds PersonIds
-------- ------------- ------------
1 1,4 1,2,8
2 1 3,5,6
3 1,4,5 4
4 4,5 7
*/[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2008-08-01 : 06:50:04

Cheers, think I get that! Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources