Combining multiple rows into one row, Oracle
Posted
by Torbjørn
on Stack Overflow
See other posts from Stack Overflow
or by Torbjørn
Published on 2010-04-22T11:07:36Z
Indexed on
2010/04/22
12:53 UTC
Read the original article
Hit count: 318
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.
© Stack Overflow or respective owner
