Combining multiple rows into one row, Oracle
- by Torbjørn
Hi. I'm working with a database which is created in Oracle and used in a GIS-software through SDE. One of my colleuges is going to make some statistics out of this database and I'm not capable of finding a reasonable SQL-query for getting the data.
I have two tables, one with registrations and one with registrationdetails. It's a one to many relationship, so the registration can have one or more details connected to it (no maximum number).
table: Registration
RegistrationID Date TotLenght
1 01.01.2010 5
2 01.02.2010 15
3 05.02.2009 10
2.table: RegistrationDetail
DetailID RegistrationID Owner Type Distance
1 1 TD UB 1,5
2 1 AB US 2
3 1 TD UQ 4
4 2 AB UQ 13
5 2 AB UR 13,1
6 3 TD US 5
I want the resulting selection to be something like this:
RegistrationID Date TotLenght DetailID RegistrationID Owner Type Distance DetailID RegistrationID Owner Type Distance DetailID RegistrationID Owner Type Distance
1 01.01.2010 5 1 1 TD UB 1,5 2 1 AB US 2 3 1 TD UQ 4
2 01.02.2010 15 4 2 AB UQ 13 5 2 AB UR 13,1
3 05.02.2009 10 6 3 TD US 5
With a normal join I get one row per each registration and detail. Can anyone help me with this? I don't have administrator-rights for the database, so I can't create any tables or variables. If it's possible, I could copy the tables into Access.
