result to produce single row from multiple rows
-
I have three tables
1. Street_house_addresses containing
- property_ID
- house_number
- street_name
2. Street_house_owners containing
- property_ID
- owner_ID
3. Owners containing
- owner_ID
- owner_name
A house may be owned by 1 to 20 different owners, however I want a single record returned showing only the first 5 owners for each house. The resulting record to be showing;
- Property_ID
- House_number
- street_name
- owner_name1
- owner_name2
- owner_name3
- owner_name4
- owner_name5
The above fields form part of a larger selection of fields also relating to the property from other tables.
I have been advised that crosstabs may be used, or cursors, but have no idea how as I am a rank beginner at SQL.
Any help would be most appreciated.
Edited by - Lashams on 12/23/2003 3:46:35 PM
-
I didn't try to write this precisely, but the way I've done it looks vaguely like this:
select field1, field2, field3, -- << regular fields
max(case when col1_condition then field4 else null end) as Col1,
max(case when col2_condition then field4 else null end) as Col2,
max(case when col3_condition then field4 else null end) as Col3,
etc.
from table1
inner join table 2 on whatever
group by field1, field2, field3
What happens is the result set (prior to the groupping) contains one row per column. As it groups, the conditional selects only one row for each of the horizontal columns, and the MAX (or sum or whatever works for you) is not really to select a maximum but to be an aggregate -- though if you had multiple roles per horizontal desired column the aggregate may actually do something.
The hard part in your case is to find a condition that detects the 1st, 2nd, 3rd, etc. address. If they have some kind of ordinal values it is easy, if not you may need an inner join over something else like an identity that ranks (e.g. the condition is the number of records with PK less than or equal to the current record for this property, plus one).
-
Hi Ferguson, thank you for taking time out to respond. I am afraid however that I do not understand your example and explanation. From what it sounds like you suggest I need a sequence number in the link file (2 - Street_house_owners), however no such sequence number exists, which is making this task difficult. To build a conditional select based on excluding each of the previous columns selected value will result in a huge SQL statement as each successive column selection will have to exclude all the previous include selections (I don't believe I can simply state the resulting field names as an exclude). I think this was why it was suggested I use a cursor but I really haven't been able to work out from examples how these work as yet. Examples always seem to be to a level of detail for experienced SQL developers rather than the likes of me, rank beginners. If you can add anything further please do, otherwise thanks again and Happy New Year.
-
It's pretty easy to make your own sequence number from any list that has unique values by just doing an inner select and count greater (or less). Here's an example from Northwind that you can run and play with. While it looks bad, as long as you are indexed on the inner select's where clause fields it usually runs pretty fast; I've had good luck with very large databases and doing things like this:
select LastName, FirstName,
max(case when Seq = 1 then TerritoryID else null end) as Territory_1,
max(case when Seq = 2 then TerritoryID else null end) as Territory_2,
max(case when Seq = 3 then TerritoryID else null end) as Territory_3,
max(case when Seq = 4 then TerritoryID else null end) as Territory_4,
max(case when Seq = 5 then TerritoryID else null end) as Territory_5,
max(case when Seq = 6 then 'There are more' else 'No more' end) as MoreFlag
from
(
select e.LastName, e.FirstName, et.TerritoryID,
( select count(*)
from EmployeeTerritories et2
where et2.EmployeeID=et.EmployeeID and et2.TerritoryID > et.TerritoryID
) + 1 as Seq
from Employees e
inner join EmployeeTerritories et on e.EmployeeID=et.EmployeeID
) as e
group by LastName, FirstName
-
Thanks Ferguson, this worked (looks bad as you said, but actually worked), I've now got to work all the joins out to make this work with my actual data set which has a few more joins to apply to build the names. I'm sure it will look totally ugly when I'm finished but you have certainly helped me along.
Cheers
Stephen
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply